In my previous blog SSIS – ETL Audit Table with Variables Passed to SQL Task, I have showed you how to read a Single row value from a table into a SSIS variable.

Here is a re-cap:

Step 1: Create the user variable in the variable window

Step 2: Create a SQL task to populate the variable varETLMode from a SQL table

Set the ResultSet to Single row, and enter the SQL script in the SQLStatement.

In the Result Set tab, enter the field name as the Result Name (needs to be the same as the field name in the SQL script), and the user variable in Variable Name.

Notice that we set the ResultSet to Single row.

What if our result has multiple rows, for example a list of dates, which we want to use to loop through each date to get some date-dependent results?

The key is to set the ResultSet to Full result set, instead of Single row.

Step 1: Create variable vLoopSet as Object for Data Type. The initial value is automatically set to System.Object.

Step 2: Create a Execute SQL Task to read from SQL query and store the results to vLoopSet.

In the General tab, set ResultSet to Full result set.

And enter a SQL query as SQLStatement.

In the Result Set tab, add a result set and set 0 for Result Name, and User::vLoopSet as Variable Name. 0 (zero) is the index number from the results of the SQL query. Index 0 (zero) will give us the first column from the SQL query.

In the next blog, I’ll show how to use this set variable together with a “Foreach Loop Container” to loop through the list days by passing a variable to a OLE DB Source in a Data Flow.