question

liton avatar image
liton asked

SSIS dynamic excel tab

I have a sql table that I need to split into more 90 excel sheet based on a code. I could create an ssis package and use conditional split and create more than 90 excel sheet. But creating more than 90 excel sheet one at a time will be time consuming and if I have to use that package again for another table then I would have to make changes. Is there an easier/faster way to achieve split a table into more than 90 excel tabs? Is it possible to use foreachloop and dynamically split and create excel tab?
ssisexcel
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
iainrobertson avatar image
iainrobertson answered
Presumably the rules that you have for splitting the data can be expressed in terms of parameters to a query? If so: - Create a metadata table to hold the parameter sets - Read this data into an ADO recordset - Pass this set to a for...each loop - Create a stored procedure that accepts the parameters as inputs - Execute the stored procedure for each set of parameters and return the data - Use a script task to add a new worksheet to your workbook - Push the data to your worksheet
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.