Import Header-Line tables into Dynamic Excel destinations II

After talking about how to use a ForEach Loop container to iterate through a query result set in the first part of this post; I am going to show how to create a distinct excel file on every iteration dynamically.

This functionality will be added over the package I built in the first part. I will basically use an Execute SQL Task with an Excel Connection manger to create a excel file with a different name on every iteration. The connection manger uses an expression in ExcelFilePath to make the file name distinct each time.

Ok, no more talking and let’s get to work!

Add an Execute SQL Task inside of the ForEach Loop Container and create a precedence constraint (yes, a green arrow!) from the Execute SQL Task to the Dataflow task.




Open the Execute SQL task and change the ConnectionType to ‘Excel’, Choose the Excel Connection Manager in Connection property and write a Create table statement in the SQLStatement as follows:

CREATE TABLE `Excel Destination` (
`HeaderID` INTEGER,
`HeaderName` NVARCHAR(50),
`LineID` INTEGER,
`LineName` NVARCHAR(50),
`LineDetails` NVARCHAR(50)
)

As you can see, the objective of this EST is to create the excel file to make it available to the data flow task on every iteration; and it is here where the power of the expression come handy.


Create a new variable, to hold the file name to be creates. I created a variable called 'ExcelFileName' of String Type (give an intial name in the variable window to avoid validation errors).

Now, use an expression to change the value of the variable at run time; to do so, go to the preperties windows of the Variable and change the porperty EvaluateAsExpression to true and click on the expression property to bring the expression editor. That expression will change the value of the variable as the package iterates through the recordset. I used an expression like:

"C:\\Temp\\Report-"+ (DT_STR, 25,1252)@[User::HeaderID] +".xls"

In the Excel file connection manager, let's create another expression that uses the 'ExcelFileName' variable to change the ExcelFilePath. To do so, click in the connection manager, go to the properties window and click in the expression property. Once the Property Expression Editor is available; choose ExcelFilePath and bring the expression builder. Write an expression like:

@[User::ExcelFileName]

Now, cross the fingers, run the package and hopefully you will get 3 excel files with following data (rows in each red box will go to a diffrent file)

UPDATE: I have posted a Follow-up with addressing few details and questions posted in the comments: http://rafael-salas.blogspot.com/2008/03/ssis-and-dynamic-excel-destinations_01.html