April 5, 2010 at 2:03 pm
I need to write a package that will extract data from a table and write into 50 flat files depending the from what state our clients are. I would like to avoid doing 50 data flow. Is they a way to do only 1 data flow and depending of the value of the state field, write in the corresponding field.
Thanks,
Rem
April 6, 2010 at 8:03 am
You could use a script component/task to write the data to the different .csv files. You can find code snippets for doing this all over the web. The only thing you have to do is to adapt the source the assign the data dynamically to the right file. (an option is to use a loop. In each iteration of the loop you write to one file and you select a subset of the data (according to a status)).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 6, 2010 at 8:32 am
The answer to your question is yes; it just depends on how you want to do it.
One straightforward, but clunky option:
Set up one data flow with one data source. Configure your data source to bring in the data from all states. Set a conditional split to split your data into 50 different flows. Set each of those flows to dump into the state-specific flat file. This will take you a little time to set up, but is fairly straightforward. If your naming convention for your files changes or you make any other slight configuration change, it will be a headache to change the package, but if your structure remains consistent and won't require manipulation any time...ever...this will work for you.
A more robust (and more supportable) option would be to utilize a For Loop or For Each Loop utilizing variables for your source query, data flow destination connection, etc. You'll have to decide how you want to enumerate or step through your files. For this package, you can enumerate through a list of states in a database table somewhere, through a list of files in your destination directory (Foreach File Enumerator), or through a list of states that you set up in a FEL (Foreach Item Enumerator).
Just depends on how you want to do it and your comfort level with SSIS.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply