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.

Thanks
Bryan

Repeat Header / Keep Header Visible in Tables in RS 2008

You selected "Repeat header rows on each page" or "Keep header rows visible while scrolling" in the tablix properties dialog, but it doesn't seem to work as expected? You might want to try the following four steps if you are using a "table"-style layout:

1. in the grouping pane, click on the small triangle and select "Advanced Mode" to show static members:
   
   
2. verify that you have the properties grid turned on (in BI Development Studio use F4, in Report Builder go to the "View" tab and select "Properties")
   
3. select the corresponding (static) item in the row group hierarchy
   
4. in the properties grid:
   - set KeepWithGroup to After
   - set RepeatOnNewPage to true for repeating headers
   - set FixedData to true for keeping headers visible

Reference link: