This post is a follow up to some of the questions I have received in a couple of post I made a while back, that showed how to create an X number of Excel file destinations.
Let’s say that you found this post that shows how to create a new Excel file on every iteration of a ForEach container; but that what you really want is to create different sheets on a single file on each iteration instead.
That is certainly easy to do by making few changes to the package in the original post.
1. Create a new variable to hold the Sheet name (@[User::SheetName]). The valuae in this variable will be changed on each iteration using an expression:
The naming convention for the sheet name would be Header-X where X represents the HeaderID from the Header table
2. Create a new variable to hold the Create table statement (@[User::CreateSheetStatement)that will create each sheet. Notice that this statement is the responsible for the creation of each sheet in the Excel workbook.
"CREATE TABLE `"+ @[User::SheetName] + "`(`HeaderID` INTEGER, `HeaderName` NVARCHAR(50), `LineID` INTEGER, `LineName` NVARCHAR(50), `LineDetails` NVARCHAR(50))"
3. Set the SQLSourceType Property of the Execute SQL task inside of the For Each container to Variable and choose the Variable you created in step 2
4. Remove the expression from (@[User::ExcelFileName); this is set the EvaluateAsExpression property to False. This is required as you don’t want to create a new excel file on each iteration, hence the excel file name in the connection manager will not change during the execution of the package.
5. In the Excel Destination Component, Change the data access mode to ‘Table Name or View Name Variable’ and choose (@[User::SheetName) from the variable dropdown list.
It is important to remember that SSIS validates connections, and in this case, it checks the excel file name and sheet name provided at design time; so while you are editing the package in BIDS, make sure that Excel file is available. Also, remember to set the DelayValidation property of the Excel connection manager to True, to avoid validation errors when the package runs.
You may want to put some logic in the package to move the Excel file to a new location or to change the file name on the subsequent executions to avoid errors like:
Table ' already exists. (Microsoft JET Database Engine)
Here is a copy of the new package: