For some solutions in SSIS you may need to go over all records from some SELECT and each record use in some subqueries. In this case you want loop record per record in some task series. Best solution is to use Looping through recordset. Because of SSIS is intuitive enough to create this task, here is step by step overview. In this step by step tutorial, I am using AdventureWorks database from Microsoft. This database was created for testing purpose and is ideal for our example.

Step 1 - Create source recordset

As first we must create new Execute SQL task and open its parameters.

 

In general settings ResultSet must be Full result set. To SQLStatement we can put any SQL query we want use as source for Loop through. I am using in example this query:

SELECT ProductID, [Name]
FROM Production.Product with (nolock)
WHERE ListPrice > 50 AND ListPrice < 100

The most important part is in Result Set page. We specify that we want use a variable for this recordset. Right now we need to create some variable. In Result Set tab in Variable Name column you can select <New variable ...> and create it.

In this example I am calling it objProduct. This variable must be OBJECT type.

When we create it, we have in the Result Name column something like this "NewResultName". This must be overwritten to "0". Because of SSIS is not working properly without numbers.

Step 2 - Create loop

Now we can create Foreach Loop Container by drag and drop and connect our Query Task with this Container.

We have to open properties of this container now and open Container page. Select Foreach ADO Enumerator. Then select objProduct from previous step as an ADO object source variable. Enumeration mode should be set to Rows in the first table.

On next page, Variable Mapping, here we can setup variables using inside loop. You select variable (if it doesn't exist yet you can create it from here) and specify which column value will be inserted into by Index number. First column in resultset is 0, second is 1 etc. 

The loop environment is ready and we can create whatever we want inside loop.

Step 3 - Inside loop

Right now you are able to create whatever you want based on record by record walkthrough. Just for example I will create simple Script Task now. This task will print product name for each record from resultset.

I created Script Task and set variables for reading which I want to use.

Inside the Design Script I am using just simple Visual Basic script, that shows a message box for each loop:

MsgBox(CType(Dts.Variables("ProductName").Value, String))
Dts.TaskResult = Dts.Results.Success

Download example

And that's it. Because best is just take a look at example and play with it, here you can download this example SSIS package and try to work it.

Download example SSIS package

Tag it: