SSIS – “Full result set” to a set variable of Object
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.
how would i store the result to variable in Data Flow? ie. OLE DB Source result to variable destination?
I haven’t done that before. But I think you can try these 3 options:
1) use the Script Component as a Destination, and then write a simple script to write to your variable
2) use the OLE DB Destination, and set the Data access mode to Table name or view name variable, and then pick your variable from the dropdown list
3) use Recordset Destination, and pick your variable as the destination.
Sherry
Is there a way to limit the result set based on the value of one of the fields?
In step 2 “Create a Execute SQL Task to read from SQL query”, you can customize your query in SQLStatement to limit the result set with any where clause.
Sherry
Hi,
how we will write code in ssis script to assign the variable having full resultset to a local parametre and send this details to users through mail.
I have added script task to compose mail but unable to write the code for it.
Thanks in advance,
chinna
Do you have the continuation of this blog, I want to read more on the ‘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.’