Home > SSIS Best Practices > SSIS – “Full result set” to a set variable of Object

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

image

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

image

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

image

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.

image

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.

image

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.

image

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.

image

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.

Categories: SSIS Best Practices
  1. ashish
    May 1, 2011 at 7:08 am

    how would i store the result to variable in Data Flow? ie. OLE DB Source result to variable destination?

    Like

    • Sherry
      May 1, 2011 at 7:00 pm

      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

      Like

  2. Charlise
    July 14, 2011 at 6:14 pm

    Is there a way to limit the result set based on the value of one of the fields?

    Like

    • Sherry Li 李雨
      July 15, 2011 at 5:18 am

      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

      Like

  3. chinna
    July 27, 2011 at 7:05 am

    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

    Like

  4. priyaks
    November 29, 2011 at 9:46 pm

    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.’

    Like

  1. March 6, 2011 at 5:43 am
  2. August 27, 2011 at 3:24 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: