question

alen teplitsky avatar image
alen teplitsky asked

dynamically splitting SSIS flow into 12 files

i have a table with approximately 500 rows that i need to dump to 10 or so excel files the table has around 8 columns with only column being a batch number with 10 or so unique values. each file has to be around this value. i was thinking of using a conditional split but it looks like it may not work. what is the best way to send a list of the batch numbers and have SSIS run a select * with a batch number in the where clause and create a different file for each batch number?
ssis
10 |1200

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

John D avatar image
John D answered
I would recommend using a SQL Task to obtain your distinct Batch Number unique values. Then you can use the For Each Looping Container to loop through each Batch Number and create a separate file with the contained Batch Number. You will be able to pass the unique value on the Where clause of the query.
10 |1200

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

Pavel Pawlowski avatar image
Pavel Pawlowski answered
+1 to @JohnD, however if each excel file has different number of columns or different column names, you have to have separate excel destination for each excel file. Then if you have separate destination for each excel file then for each excel file you will have a separate data flow or a conditional split inside the data flow to direct the data to right excel destination. As probably better solution could be to create a separate package for each excel file (not exactly for each excel file but for each column structure). Then from a master package within a For Each Loop container as @JohnD mentioned you can execute a particular child package for corresponding table structure and pass appropriate parameters with necessary information to process the data.
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.