I found a bunch of articles out there with instructions on this topic but I had a hard time following them and really understanding what I needed to do to get some results into an Excel file and name that file dynamically. I couldn't believe how tricky this was. After I finally figured out the steps, I thought I would type up the steps for my future reference and if someone else benefits, even better. Good luck!
Note: These instructions are based on Microsoft SQL Server Integration Services 2008 R2
1. Create a Template Excel File
This is just for the purpose of getting started with the necessary steps in SSIS. In this example, I will be creating an Excel file with the .xls extension (Version 97-2003). I had some difficulty with the 2007+ version when working on this.
2. Create a variable
3. Create the Excel Connection
4. Add expression to Excel Connection
Go to the Properties for the Excel Connection Manager created in step 3
5. Task 1 - Execute SQL Task
This step is to give the Excel file a dynamic name. For example, you may want your Filenames to end up something like "Customers_20130403.xls" to indicate the output file for April 3, 2013. This could be also a Script task. Anything that allows you to modify the contents of your User::ExcelFilename variable.
6. Task 2 - File System Task
This step will delete the target Excel file if it exists. This is necessary if the package is executed again (on the same day for our example).
7. Task 3 - Execute SQL Task
This step creates the Excel file and the target worksheet within it (Sheet1). Think of Sheet1 as an empty table that is created that will be populated in our next step.
Modify this example to match your columns and data types in your target Excel file.
Notice that the single quotes are actually the character under the tilde ~ on the keyboard, not the typical single-quote character.
CREATE TABLE `Sheet1` (
`Last Purchase Date` DateTime )
8. Task 4 - Data Flow Task
This step is to populate your Excel file.
9. Last Steps
Note: If you delete the template Excel file (Book1.xls in this example), you'll get errors when you re-open the SSIS package in Visual Studio as it does validation. You can fix this by changing the initial value of the User::ExcelFilename variable to be a path and filename of some other existing Excel file with the same columns, etc.
I hope these instructions help someone else out there.
Step 3 - Creating the Excel Connection Manager.
Step 4 - Adding an expression to the connection manager so the filename can be dynamic.
Step 6 - To overwrite the data in the same Excel file, let's first delete the physical file if it exists.
Step 7 - Create the new Excel file and the blank worksheet (table) in preparation of loading rows to it.
Step 8 - In the Data Flow Task, define where the results will end up.
Step 9 - Try setting Run64BitRuntime to True or False if you're getting errors while debugging your SSIS package.