Full Result Set

In this section, we'll demonstrate how to set an object variable to hold the full result set and get each record from the variable. Let's continue to build the package.

  1. Open the package ExecuteSQLTask.dtsx in the project LearnSSIS1 if it is closed.

  2. Disable Contain 2 and enable Container 3 in the package.

  3. Create 3 variables FullResult, Name and Age as below.
  4. SSIS Define 3 Variables

    The FullResult is defined as Object data type and will hold multiple rows after the SQL runs. The other 2 variables' Name and Age will hold the field value of each record.


  5. Right click "Full Result Set" task and choose "Edit..." to open the task editor and make the settings as follows.
  6. Execute SQL Task Editor Full Result

    The ResultSet field is set as "Full result set" and the SQLStatement was set as below.


    SELECT name, age FROM dbo.student
    WHERE id = 1 or id = 2
    

    After running the above SQL, the first 2 records of the table student will be returned.


  7. Click Result Set tab and then click Add button. Then change the NewResultName to 0 and set the Variable Name to User::FullResult.
  8. Result Setting For Full Result Set


  9. Click OK finish the task setting. The right click Foreach Loop Container and choose "Edit..." to open its editor.

  10. Click Collection tab, select "Foreach ADO Enumerator" as Enumerator and User::FullResult as ADO object source variable.
  11. Foreach Loop Editor Collection Setting


  12. Click Variable Mappings and select the User::Name and User::Age in the variable field. At last click OK button to save the settings.
  13. Variable Mappings Setting

    The sequence of the variables must be set as User::Name first and then User::Age because they must be matched with the Select SQL statement.


  14. Edit "Script Task" in Container 3 and add "User::Name", "User::Age" in the ReadOnlyVariables and add the following code in the Script to display the result of the variable.
  15. MessageBox.Show(Dts.Variables["User::Name"].Value.ToString() + ":" + Dts.Variables["User::Age"].Value.ToString());
    

    For details to make these settings, you can check the Script Task.


  16. Run the package and the following result will be displayed. Then click OK button you will have the name and age of the next record popped up.
  17. Full Result Set Running Result

In summary, we use an object variable to hold the full result set and use foreach loop to to get each field of the record in the variable for further process. In the next section, we'll introduce the last ResultSet - XML and parameters in SQL statements.