SSIS Dynamic Excel Destination File Name

Question

How can I configure a dataflow task that takes data from a MS SQL Server 2008 datasource and puts it in an Excel file where the filename looks like 'date filename'.xls?

Answer 1

Excel is the biggest paint to deal with in SSIS. Usually I store a template file that just has the column headers and nothing else. I start with a task to copy the template file to the processing directory. You can use variables to create the file name in an expression at this point. Alternatively, you can create the file in the dataflow and then rename the file in a step after the data flow. With text files, I have dynamically created the connection in an expression, but Excel seems to be funny about that.

Answer 2

Provided that your column definition don't change.... you can go to

1. Right Click on Excel Connection Manager
2. Expression
3. Select connectionstring
4. bulid expression (for Example : (DT_WSTR, 50) GETDATE() + @[user::FileName] +".xlsx")