I am exporting data using the Task, Export Data menu in SSMS. I want to save the export as an SSIS package. My only issue is I need today's date in the filename. I know in SSIS you can do this in an expression.
But when typing filename in the box how can I write this out?
PtSurveyList_'getdate()'?
what would be the correct syntax for the system to know I want the getdate() function not the actual word?
The import/export wizard is a streamlined editor that creates an SSIS package. It's no different than using BIDS/SSDT to create a package (unless you're using SQL Server Express Edition wherein you are not licensed to save the resultant SSIS package, only execute it).
To answer your question, you cannot accomplish what you are asking for by directly using the import/export wizard.
Use the import export wizard, save your SSIS package and then edit it with BIDS/SSDT. Unless you're on 2005/2008, you should be able to download the correct version of SSDT from the Microsoft website and edit your package locally. In the screenshot provided, that is not where you would apply the logic, you'd need to apply an Expression to the Flat File Connection Manager's ConnectionString property.
An even better approach is to not build out the date logic within the expression. This becomes brittle in situations where the server was down for a day due to patching, file was late being delivered, etc and now you have two files to process and what you built only looks for "today's" file. Now what - change the file name; change server time, edit your package? Instead, use a Foreach (file) Enumerator to pick up the file for processing. You can use a wildcard in the File Specification for it to restrict to only the PTSurveyList files. And then, obviously, use a File System Task to archive/move the processed file out of the source folder so you don't double process.
Use the import/export wizard as is. It always looks for a file called PTSurveyList.csv. If you need today's date attached to the data you import, add a column to the target table with a default constraint of GETDATE(). That will ensure you have the processed/today's date in the table.
You then need to use OS level tooling/scripting/SQL Agent to handle identifying the current days file and any file manipulation from there. I'd go PowerShell but you can accomplish this with DOS batch scripting although it'll be uglier.
Pseudologic
A "trick" to solving the dynamic date via xp_cmdshell is to dynamically build the execution string. Approximately
DECLARE @Command nvarchar(4000);
SELECT @Command = N'EXEC master.dbo.xp_cmdshell ''rename "C:\CentOS_Share\PTSurveyList_.csv" "PTSurveyList_' + CONVERT(char(10), CURRENT_TIMESTAMP, 121) + '.csv"''';
EXEC(@command);
Of course, then you can run into the double quote issue with xp_cmdshell
