Thursday, July 29, 2010

SSIS Execute SQL Task into an object variable / For each ADO Enumerator

I ran across the need to dynamically send out 2000+ emails based on a Execute Sql Task in SSIS. I knew I needed to use the For Each Loop Container but wasn't sure how to link multiple rows to a single action in the For Each Loop Container.

I ended up using the Execute Sql Task with a Result Set of "Full result set" and returned the Result Set to a single Variable Object. This allows you to process multiple columns/rows into a single variable. Just need to make sure its a Object variable. Then in the For Each Loop Container Collection I used the Foreach ADO Enumerator. This allowed me to reference the Object variable I setup earlier. Next, I mapped each of the variables in the mapping section based on the order of the columns in the extract proc. Very similar to an array in .net. Finally, I used a script task to fire off an email for each row.

I have used the For Each Loop Containers in the past for processing/imports of data files. This is the first time I have worked with the Object variable in SSIS and agree this is a very power trick to have in your toolbox.



Post a Comment