SSIS:Using RecordSet Destination with For Each Loop

When programming with ADO components, one of the more common tasks is retrieving a dataset and looping through the results while taking action upon each tuple. For example suppose you have a contact table and need to send out daily emails to each contact in the table. With .net you’d consume the data in a recordset and loop through each record to email each contact.

There’s an equivalent in SSIS which contains an ADO recordset in which you can pump the data into. However before going on and explaining how to set this up, I need to answer the comment/question in your head, which is “why consume a recordset with SSIS when there’s less overhead in managed code?”, Well first you shouldn’t be using this for large processes, it belongs in .net/Java/etc. However for smaller processes I like to follow the 3 AM rule. This rule is if you’re paged at 3 AM you should be able to troubleshoot an issue in less then 30 minutes. If the process is embedded in a black box executable, it means finding source code, running a dev env in production, etc, many hassles. SSIS offers a simple interface and componentry for troubleshooting.

In the following Example I’ll email each record in the table. For Simplification I’ve created the following table and populated it with 100 recordscreate table Mailtest

(Pk int identity(1,1),
EmailAddress varchar(25),
Name Varchar(15)
)
In SSIS create three global variables
EmailRecordset as Object
EmailAddress and String
PkTracking as int32Now in SSIS Create a Data Flow task. Use a Ole DB Source that conects to
the database you just created the table in. Write a select * from Mailtest query in your source.

For the destination choose Recordset Destination. In Custom Properties set the variablename to match your variable object EmailRecordSet. What this means is when the recordset is created will be placed into this variable.In your Input Columns tab ensure that Pk and email are represented as below. This just tells the recordset that these are the fields you’re interested in.

Once your data flow task is setup drag a foreach loop container down and ensure the workflow moves from DF to the loop as below.


Now we just need to setup the loop to work off the recordset. In the collection tab select the Foreach ADO Enumerator. Under ADO object source make sure the variable containing the recordset is selected. Under the Enumeration mode select “Rows in the first table”

Now move on to the Variable Mappings tab. Here you will assign your string an int32 variables to the output. The index should be numbered with a 0 base according to the order in which they are returned.



Once that’s in place the last thing to do is work off the data on each loop. In the case above we’re consuming the email address and emailing out. Here’s what the expression of the “to” property of the email component.

@[User::EmailAddress]