December 22, 2006

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.

11 comments:

  1. Awesome post.. Thanks for the help Rafeal..!!

    Cheers
    Scotty
    Australia - Melbourne

    ReplyDelete
  2. any ideas of doing the same with a text file? coz the text file does not have column based structure..

    ReplyDelete
  3. This is very useful, thank you. One question: HeaderEmail is selected as a column in the initial Execute SQL Task and a variable for HeaderEmail is declared, but as far as I can tell it's not used for anything. Is this an oversight or am I missing something (the latter is completely probable).

    I lied, two questions: if I attempt to execute the package a second time (because the source data has changed and I want to update/overwrite these documents) I receive the error "Table 'Excel Destination' already exists." How might I modify this package so that it would overwrite an existing document?

    Thanks,
    .o.

    ReplyDelete
  4. Viper,
    Same concept would apply for Flat File destinations; you need to use expressions in the Flat file connection manager to create a new file on each iteration.

    DM,
    You are right I am not using HeaderEmail after the first Execute SQL task. That is a bad practice as you should get rid of the unused columns/rows. Good catch!
    Regarding the error; See my Follow up post here: http://rafael-salas.blogspot.com/2008/03/ssis-and-dynamic-excel-destinations_01.html

    ReplyDelete
  5. Really Awesome.Thank you for the help Rafeal

    ReplyDelete
  6. It's really a good practice about parameter and for loop. I'm encountering the AS400 extraction issue. I need to create alias for the files with multi-members. I read another post (http://rafael-salas.blogspot.com/2007/11/ssis-mapping-parameter-inside-of.html) and create the following parameter.
    "CREATE ALIAS QGPL.MBAL" + @[user::Var1] +" FOR XXXXLIB.XXXMBAL(" + @[user::Var1] + ")". I'd like to know how th pass the resultset & parameter from foreach container to the create alias parameter.

    Cheers.

    Corey

    ReplyDelete
  7. Hello Rafael,

    I've done the extraction from multi-members on AS400 to SQL Server 2005 DB through SSIS successfully. The key components are "Foreach loop" and parameterized SQL command. Really thanks for the marvelous post.

    Cheers.
    Corey

    ReplyDelete
  8. Hello Rafeal... Any idea how to delete an existing worksheet..?

    I tried
    DROP TABLE [worksheet name]

    Thanks for the help..

    ReplyDelete
  9. Thank you for this article, you've helped me do exactly what I wanted.

    ReplyDelete
  10. Thank you for this article, you've helped me do exactly what I wanted.

    ReplyDelete
  11. Hi Raef - brilliant article however do you know if there is a way to create a SQL table in an existing database from an excel spreadsheet. For instance the spreadsheet I receive has different headers each week (w/e headers) so I want to create a new staging table each time. Do you know if this can be done?

    ReplyDelete

I will love to hear your feedback and comments. Thanks.