SSIS Package With Template to Multiple Excel Sheets

SSIS and Import/Export
1

Hello,

I am working on a project that involves generating multiple Excel sheets from a single source table of data. The source data needs to be split into separate Excel sheets based on change in a client number. Essentially each client in the source table, will need to have their own separate Excel file. I have an Excel template that needs to be used. The reason for the need for the template is that one of the columns needs to be highlighted in yellow when each of the separate Excel files are created. The format of each Excel file will be the same. The data however needs to be split based on the client number.

Additionally, as the separate Excel sheets are created they need to be named dynamically. They need to be named 'ClientNumber_ClientName_ReportTitle_YYYY.xls. The client's name is not in the source table. I need to find a way to join with the client's number in a control table or something.

Basically, the big picture is that I would like to create an SSIS package that runs a stored procedure that populates a single source table with the appropriate data for multiple clients, then splits the data into separate Excel files for each different client in the source table.

I am not sure whether I should make a Foreach loop or a Script task. I wouldn't think that a Conditional Split would work in this case. The approximate number of clients I need to create separate Excel sheets for is 150.

Thank you

2

I would use a foreach loop - and loop over the set of clients. Prior to the foreach loop I would build a data flow that pulls the list of clients - in that list I would include the client number, client name - and any other identifying information. The destination of the data flow will be a recordset object.

The foreach loop then loops over the recordset object - which gives you the client number and name in individual variables. Inside the foreach loop, copy the template file to the new filename (another variable scoped to the foreach loop) - and use that same variable in an expression on the connection to change the filename - add a data flow inside the foreach, using the client number variable on the oledb source to pull just the data for that client.

Each iteration of the loop will then create a new file - one for each client found in the recordset object.

3

I dont think you need to create a single source table. Once you fetch the data per customer you just dump it to excel. You can accomplish this also using powershell or python.

4

Hi Jeff,

I just wanted to send you a note of thanks for assisting me with this solution. It worked great!

Take Care.

5

That is good to hear - and happy to help. Thank you for the update.