how to create dynamic excel sheets based on table data

Question

Given one table in SQL Server which holds consolidated data from three source tables including one column called OFFICE which differentiates the records from each other.

The three source tables hold data from three offices.

I want to create an Excel file dynamically which will have 3 sheets in one workbook based on the three different different offices (ex. office1, office2, office3) resulting in each sheet containing the relevant data according to its office.

Please recommend an approach using dynamic Excel destination in SSIS as I don't want to use an approach which creates a template file and then copies that template to destination excel file.

Answer 1

While this can be accomplished using a scipt task and C#, a far easier solution is demonstrated at http://www.rafael-salas.com/2006/12/import-header-line-tables-_116683388696570741.html

and the follow-up

http://www.rafael-salas.com/2008/03/ssis-and-dynamic-excel-destinations_01.html#!

But to summarize the relevant details, you need to use an 'Execute SQL Task' to dynamically create the sheet at runtime prior to using it as a destination.

Create a new variable to hold the Sheet name and set this variable to the Office you are working with as you iterate through them.

Also, create a variable to hold the Create table statement that will create each sheet. For example,

"CREATE TABLE "+ @[User::SheetName] + "(HeaderID INTEGER, HeaderName NVARCHAR(50), LineID INTEGER, LineName NVARCHAR(50), LineDetails NVARCHAR(50))"

and Set the SQLSourceType Property of the Execute SQL task inside of the For Each container to Variable and choose the Variable you created to hold the create statement.

In the Excel Destination Component, Change the data access mode to ‘Table Name or View Name Variable’ and choose the sheet name variable you created from the variable dropdown list.