Historical article
- Original title: Looping through a ADO Resultset in SSIS
- Author: Brian Knight
- Original publication date: March 3, 2006
- Originally published on: White Knight Technology
- Preservation note: The technical article below is preserved as a historical SSIS resource. Its original pattern has not been removed or silently modernized.
Archive provenance: White Knight Technology Historical Archive
The original example used the AdventureWorks database and its HumanResources.Shift table. The goal was to use query results in SSIS and loop through them record by record, for example to execute a package task for each client. The article warned that this pattern should not be used over thousands of records; dozens of records were a more appropriate scale.
Create two package variables with no default value: User::objHumanResource with data type Object, and User::strShift with data type String. The original instructions placed both variables at package scope rather than inside an individual task or container.
Drag an Execute SQL Task onto the design surface, name it Read Shift Table, point it to the AdventureWorks connection, and use this query:
SELECT * FROM HumanResources.Shift;
Store the full result set in an Object variable
In the Execute SQL Task's General page, select Full result set for ResultSet. On the Result Set page, add a row, replace NewResultName with 0, and set the variable to User::objHumanResource. Result name 0 tells the task to output the first result set from the query into that object variable.
The task stores the returned ADO rowset as one object. Do not try to assign a multirow result directly to a string variable.
Enumerate and map each row
Drag a Foreach Loop Container onto the design surface and connect the Execute SQL Task's green success arrow to it. On the Collection page, select Foreach ADO Enumerator, choose User::objHumanResource as the source variable, and set the enumeration mode to Rows in the first table.
On the Variable Mappings page, select User::strShift and set the Index to 1. This maps the second column in each row to the string variable; the first column would use index 0. Variable indexes are ordinal positions and must match the query's column order.
Use the current row inside the container
Place the task that should run for each row inside the Foreach Loop. The original example used a Script Task and passed strShift in the Script Task's ReadOnlyVariables option. Its demonstration script was:
MsgBox(Dts.Variables("strShift").Value)
For a multi-client ASP-style environment, the same pattern could loop through a client list and perform a series of actions for each client. The original article also supplied an ADOVariable.zip package download; the archived link is retained in the evidence record but the binary was not reintroduced into this page.
When applying this pattern to connection managers, map each row's values to typed string variables and use those variables in the connection manager's Expressions property. A full connection string can be assembled like this:
"Data Source=" + @[User::ServerName]
+ ";Initial Catalog=" + @[User::DatabaseName]
+ ";Integrated Security=SSPI;"
If a task validates before the loop assigns its first row, set DelayValidation on the affected task or container where appropriate. This postpones validation but does not repair an invalid expression or missing value.
When troubleshooting, confirm package-variable scope, the Execute SQL Task result name 0, the Foreach enumerator, the variable-mapping indexes, the Script Task access list, and the exact connection-manager property receiving an expression.
2026 Update
This page preserves the historical Execute SQL Task, ADO recordset, and Foreach Loop pattern. Current SSIS deployments should validate the package behavior, task settings, expressions, and deployment requirements against the specific SSIS version they support before using the pattern in production.