How to Loop through each record in a SSIS in-memory ADO Recordset Table

In this post we describe Step by step How to Loop through each record in a SSIS in-memory ADO Recordset Table  ,  in Sql Server Integration Services.

In this article we create from scratch a small SSIS application which uses the Foreach SSIS item for a loop through all records in a table.
Your table can be a SQL OLE DB table , an Excel , a raw file , an ADO NET table , an XML . The source of it does not matter, since we'll copy its rows inside an in-memory table , and loop over it.
For this example, we'll loop over all records from an OLE DB SQL Table, in order to send emails to all the "Contacts" in the table.
Our pilot SSIS application will show as the following:

How to Loop through each record in a SSIS in-memory ADO Recordset Table 

First , search in the Toolbox for the Data Flow Task item:

Open it for "Edit" , and add an OLE DB Source:

In the "Edit" mode, set a Connection Manager , and add the following SQL Command:

As you can see, our command loads all ercords in the Contact table from Adventure Works:

Next, add (this is optional) a Derived Column item , for editing the columns, if you need it.

For this example, we don't edit it. Just drop this item in the Data Flow.

Next, append a Recordset Destination item, in order to keep all fetched records in RAM memory.

Your Data Flow Task should now look as this:

At this point, the Recordset Destination will require an Object variable where to keep the records.

Therefore, create a new variable with "Package" scope as follows:

And then, set the  Recordset Destination's  "variable name" to this variable:

And now, select all the columns that you want to be included in the in-memory table:

Take a close look at the ORDER of this columns: this is going to be very important in the next steps.

Now, add a Foreach Loop Container, which will loop through the in-memory Table:

In every loop iteration , the current record's data will be copied to the correspondent variables, which now we are going to create as follows:

You must respect the types of the columns being copied: NVARCHAR goes to STRING, and so on.

Inside the  Foreach Loop Container, in "Edit" mode, set the Enumeratoor to  "Foreach ADO Enumerator" :

And tell SSIS that you want to loop through the Object variable that you set before:

Also select the "Rows in the first table" option.

Now, set the variable mappings EXACTLY IN THE ORDER that they were set inside the ADO in memory table:

That means, the current record value for the column "ContactID" will be copied to the "iContactID" .integer variable, and so on

That's all!!!! 
Now you will use that loop for example to send emails to all the Contacts in the table, setting the required parameters with the values from the loop variables: 

Hoping this article was useful to you...

      by Carmel Schvartzman

כתב: כרמל שוורצמן