Brian Knight, SQL Server MVP

Looping through a ADO Resultset in SSIS

 Sometimes there is a special case where you’d like to use the results of a query in SSIS and loop through it record by record to perform a series of tasks. You should use these types of incidents very carefully since SSIS wasn’t truly meant to do this over thousands of records. This is useful however if you have a client table and you wish to loop through that table and execute an Execute Package task to load that client’s data. There are many other uses I’ve found for this but the client example is probably the central one.

Let’s try a quick example using the AdventureWorks database to execute a Script task inside the loop for each variable. To configure this, let’s first go ahead and create a connection to the AdventureWorks database. With the connection created, we’re going to pull data from a small table (HumanResources.Shift) to do a quick demonstration.

Next, create two variables with no default value. The objHumanResource variable will have the data type of Object and the strShift variable will be a string. You can create a variable by right-clicking in the design area and selecting Variables. Make sure the variables have the scope of the entire package and not an individual container or task.

Drag an Execute SQL task onto the design pane. Name the task Read Shift Table and point it to the AdventureWorks connection. Use the following directly inputted query:

SELECT * FROM HumanResources.Shift.

In the General page of the Execute SQL task, select Full result set for the ResultSet option. This option tells the task that you wish to output the results of the query to some variable. Go to the Result Set page and add a new line in the Result Set grid by clicking Add. For the Result Name column, replace NewResultName with 0 and set the Variable Name column to User::objHumanResource. The final screen should look like the below screenshot. This is telling the task that you wish to output the first result set from the query (0) to the variable.

Drag over a Foreach Loop Container and connect the green arrow from the Execute SQL task onto the container. In the Collection page, select Foreach ADO Enumerator. Select the source variable of User::objHumanResource and the mode should be set to Rows in the first table.

For the Variable column, select User::strShift. For the Index column type 1. This is making the container output the 2^nd column from the rowset into the variable as it iterates through the loop. If you wanted the first column, you'd use the Index of 0.

Now, you can do whatever you want inside the container. For my example, I placed a Script task inside the container and made a little Hello World example by using the following script (and of course passing in the strShift variable in the ReadOnlyVariables option of the Script task):

MsgBox(Dts.Variables("strShift").Value)

That’s all there is to it. Again, you would never want to use this on thousands of records but dozens of records would scale nicely. The main use I use it for is to loop through a list of clients in a multi-client ASP type environment and perform a series of actions with it.

You can download the package here to get you started.

-- Brian Knight