Tuesday, May 19, 2015

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:

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

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


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


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

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

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


through each record in a SSIS in-memory ADO Recordset Table


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:


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

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:


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


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

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

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

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

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:


 SSIS in-memory ADO Recordset 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:

 in-memory ADO Recordset Table

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" :


How to Loop through each record

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


 SSIS in-memory ADO Recordset Table


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:



How to Loop through each record in a 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: 


How to Loop through each record in a  in-memory  Table

Hoping this article was useful to you...


      by Carmel Schvartzman


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