Dynamic split of data in SSIS 2008
Posted on 2011-03-17
Looking for suggestions on the best way to handle this situation. I have multiple SSIS packages for processing financial transactions. The first process has a file watcher on an FTP folder that sees a file hit and imports the transactions into a staging table for later processing. this process is event driven so it can happen multiple times in a day as files get deposited.
The second process is manually triggered by a user once conditions in our system have been met and transactions are ready to be processed. When the user triggers this process I need to grab all the transactions that have not been processed from the staging table and transform them and insert into other tables for a third party application to work with.
My problem lies here, when I grab the transactions to be processed, I need to divide them into like batches with like dates. So all buys for March 3rd in one batch, buys for March 4 in another, sells in another, etc. It doesn't appear that a conditional split will work because of the expressions needing to be hard coded. We have over 460 different transaction codes and manually creating a task process for each is not something I look forward to.
So, how would you suggest I use SSIS to take this single recordset from the staging table and divide it into other datasets each representing a group with a common date and transaction code for the entire set to be processed? I am thinking the only way I may be able to do this is with a script task, but can I dynamically create variables to hold the different datasets? Would I have a static Object variable to hold an array of dataset names and iterate through that list to call all the dynamic datasets I need to process?