Single Row Result Set

In this section, we'll demonstrate how to set variables to get the single row result set. Let's continue to build the package.

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

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

  3. Create a variable Count with Int32 data type and 0 default value.
  4. SSIS Define Count Variable

    The variable will be used to hold the single result which is the number of the records in table student.


  5. Right click "Single Row" task in Container 2 to open Execute SQL Task Editor and make changes like below.
  6. Execute SQL Task Editor Single Row

    The ResultSet is changed to "Single row". The SQL statement is set as below. The Amount is the output field.


    SELECT COUNT(*) AS Amount FROM dbo.student
    

  7. Click "Result Set" tab on the left, then click "Add" button and change the Result Name to Amount which is the same as the output field name in the SQL statement. At last change the Variable Name to "User::Count".
  8. Result Set Setting For Single Row


  9. Click OK button to finish the setting of "Single Row" task.

  10. Edit "Script Task" in Container 2 and add "User::Count" in the ReadOnlyVariables and add the following code in the Script to display the result of the variable.

  11. MessageBox.Show(Dts.Variables["User::Count"].Value.ToString());
    

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


  12. Run the package and you will get "3" in a popped up window. That's the number of records in student table.
  13. Single Row Result Set Running Result

Note Note
When the ResultSet is set as Single row in the Execute SQL Task, the Result Name in Result Set must be the same as the field name in the SELEct statement as well as the number of fields in the Result Set is the same as the number of the outputted field names.

In the next section, we'll introduce the full result set setting in a Execute SQL Task.