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

SSRS 2008 Inscope / Percent to Total

Over the past couple of weeks I have been asked several questions about InScope function in SSRS 2008. It is a very useful function that allows for aggergations at different group levels in a report. This is helpful when trying to calculate a dynamic percent to total by a parent group.

Here is an example of how to use Inscope:
Territory Total of 2500 appts
District Total of 125 appts
what is the percent total?

=iif(iif(InScope("Territory", count(Fields!.Appts.Value,"Territory"),0) = 0 , 0,
count(Fields.Appts.Value) / iif(InScope("Territory"), count(Fields.Appts.Value, "Territory"),0))

Result = 5%