In my SSIS project, I need to output CSV files after performing transformations in SSIS. All the generated files should be stored in a predefined path (configured as connection string) using a dynamic filename generator (something like TEST-ddmmyyy.csv).
I understand this is possible using expressions. But I want this to be passed as project parameter for easy deployment. Since expressions can't be used in Project parameters, Is there any other possibility or alternatives ?
Pass the format you want for the file name in as a parameter (TEST-ddmmyyyy.csv, in your example), and then use expressions to replace parts of the string as necessary. In your example, you could pick up the date at runtime, and replace the ddmmyyyy string with the relevant numbers.
Exactly how to do it would obviously depend on the nature of the format you need for the filename, but at a high level the above sounds like it should do the trick. If you need a unique ID rather than the date you could store the last ID number or current ID number in a config table, or even a file if a table isn't an option.
User:Jo Douglass gave the logical solution to the problem. This answer to a SO question gives a detailed rundown on why we cannot use Expressions in Project Parameters and an alternative approach to solve the issue.
