Import Header-Line tables into Dynamic Excel destinations I

I saw a post recently in the MSDN SSIS forum asking for help to build a package to loop through the rows of a ‘Header’ table. Then for each ‘Header’ row; get rows from ‘Line’ table and then import them into an Excel file, this is, generating a different Excel file for each Header-lines set.

For the sake of simplicity, I decided to divide the solution in two parts. This first part explains how to use a ForEach Loop Container to iterate through a result set retrieved by a query. Then Second part shows how to use Excel Destination to create files dynamically.

Let’s first create the tables and some sample data for our data source

CREATE TABLE [dbo].[Header](
[HeaderID] [int] NULL,
[HeaderName] [nvarchar](50) NULL,
[HeaderEmail] [nvarchar](50) NULL
) ON [PRIMARY]

INSERT INTO [Header] ([HeaderID],[HeaderName],[HeaderEmail])VALUES(1,'ABC','Abc@abc.com')
INSERT INTO [Header] ([HeaderID],[HeaderName],[HeaderEmail])VALUES(2,'CDE','cde@cde.com')
INSERT INTO [Header] ([HeaderID],[HeaderName],[HeaderEmail])VALUES(3,'GHF','ghf@ghf.com')

CREATE TABLE [dbo].[Line](
[LineID] [int] NULL,
[HeaderID] [int] NULL,
[LineName] [nvarchar](25) NULL,
[LineDetails] [nvarchar](50) NULL
) ON [PRIMARY]

INSERT INTO [line] ([LineID],[HeaderID],[LineName],[LineDetails])VALUES(1,1,'Line1','xxxx')
INSERT INTO [line] ([LineID],[HeaderID],[LineName],[LineDetails])VALUES(2,1,'Line2','yyyy')
INSERT INTO [line] ([LineID],[HeaderID],[LineName],[LineDetails])VALUES(3,1,'Line3','zzzz')
INSERT INTO [line] ([LineID],[HeaderID],[LineName],[LineDetails])VALUES(4,2,'Line1','ttttt')
INSERT INTO [line] ([LineID],[HeaderID],[LineName],[LineDetails])VALUES(5,2,'Line2','ggggg')
INSERT INTO [line] ([LineID],[HeaderID],[LineName],[LineDetails])VALUES(6,3,'Line1','jjjjjjjjj')

The Package stuff

In an empty package, add a Execute SQL Task and a ForeachLoop Container. The Execute SQL Task is used to get the ‘Header’ set of rows that will drive the ForEach loop container.



Then create a SSIS variable of ‘Object’ type to hold the result set from the Execute SQL Task, in this example I called it ‘HeaderResultSet’. Also create one variable for each column in the ‘Header’ result set you are planning to use. In this case I have added one for the HeaderId and other for the HeaderEmail column.






Open the Execute SQL Task and write the SQL Statement to query the Header table, change the ‘ResultSet’ property to ‘Full Result Set’. The connection property obviously has to point to the database where you created the sample tables, therefore make sure you create a connection manager for that.

In the ResultSet tab, select the variable that will hold the query result and click OK.

This is the query I eneterd:

Select HeaderID, headerEmail
From dbo.Header

So far, we have configured the Execute SQL Task to retrieve a set of rows from the ‘Header’ table and place the result into a SSIS variable called ‘HeaderResultSet’.

Now, the ForEachLoop container. Go to the collection page and make the selections as shown bellow:

Next, go to the variable mapping tab and create a mapping for each column in you result set; in this case the HeaderID and the HeaderEmail

Now, add a DataFlow task inside of the ForEachLoop.

Inside of the dataflow task, add an OLE DB Source. This Source component will use a parameterized query to get the ‘line’ rows of each ‘Header’. Use the same connection manager you used in the Execute SQL Task, choose SQlCommand as data access mode an write a query like:

SELECT H.HeaderID, HeaderName, LineID, LineName,LineDetails
FROM dbo.Header H Join dbo.Line L on H.HeaderID = L.HeaderID
WHERE H.HeaderID = ?

Once the query is in place, it is time to map its parameter. Click in the parameters button and create the required mapping. In this case, the variable HeaderID, that is populated by the ForEachLoop container, is mapped to the HeaderID column in the WHERE clause of the query. Click OK when you finish.

Now, back to the data flow, add an Excel Destination component and connect the OLE DB Source output to it.

Then, open the Excel destination and create the connection manager and the excel sheet using the new buttons. For this example, the default selections should work just fine. Notice that when you use the new button to create the Excel Sheet a create table statement is issued against the Excel workbook to actually create a sheet. This would be used again when building the logic to make a ‘Dynamic’ Excel destination.

By now, the data flow should look like:

At this point, you should be able to execute the package and get all the rows into a single Excel file; which is half of our ultimate goal.

If you are still interested in the other half of the article and learn how to make a Dynamic Excel Destination, just check it out here.