Hadi Fadlallah
This image shows where to change the Result Set type in Execute SQL Task in SSIS

Execute SQL Tasks in SSIS: Output Parameters vs Result Sets

September 17, 2019 by

In the previous article, I gave an overview of Execute SQL Task in SSIS and we illustrated some of the differences between writing an expression to evaluate SqlStatementSource property and writing this expression within a variable and change the Execute SQL Task Source Type to variable.

In this article, I will describe the difference between using output parameters and result sets within Execute SQL Task in SSIS.

Note that this article is the fifth article in the SSIS feature face to face series, which aims to remove confusion and to illustrate some of the differences between similar features provided by SQL Server Integration Services.

Parameters

Execute SQL Task in SSIS allows user to execute parameterized SQL statement and create mapping between these parameters and the SSIS variables. To add a parameter into a SQL statement you must use a parameter marker which differs based on the connection type.

Connection Type

Marker

Example

ADO

?

Select * from table where ID > ?

ADO.NET

@<parameter name>

Select * from table where ID > @ID

SQLMOBILE

@<parameter name>

Select * from table where ID > @ID

OLEDB

?

Select * from table where ID > ?

ODBC

?

Select * from table where ID > ?

EXCEL

?

Select * from table where ID > ?

There are three types of parameters that can be used within an Execute SQL Task in SSIS:

  1. Input parameters: used to pass a value as a parameter within a SQL command or stored procedure
  2. Output parameters: used to store a value generated from an SQL command or stored procedure
  3. Return Value: used to store a value returned by an SQL command or stored procedure

When using Execute SQL Task in SSIS, to map a parameter to a variable, you must go to the Parameter Mapping tab, and define the variable mapping.

As example, in the Execute SQL Task, we defined the following SQL command:

Now, if we click on the Parameter Mapping tab, we should see the following form:

This image shoes the Parameter Mapping Tab within Execute SQL Task in SSIS

Figure 1 – Parameter Mapping Tab

To add a parameter mapping, we must click on the Add button, since we have on specified one parameter in the SQL command then we should add only one mapping. When we click on the Add button, one line is added to the grid as shown in the image below:

This image shows how Parameter Mapping is added in Execute SQL Task in SSIS

Figure 2 – Adding Parameter Mapping

Within Execute SQL Task in SSIS, you have to configure the following properties of each parameter:

  • Variable Name: Select the variable name that you want to map to a parameter
  • Direction: Specify if the type of the parameter (input, output, return value)
  • Data Type: Specify the data type of the parameter (It must be compatible with the data type of the variable)
  • Parameter Name: The name of the parameter, the naming convention depends on the connection type:

    Connection type

    Parameter name

    ADO

    Param1, Param2, …

    ADO.NET and SQLMOBILE

    @<parameter name>

    ODBC

    1, 2, 3, …

    EXCEL and OLE DB

    0, 1, 2, 3, …

  • Parameter Size: Specify the length of the parameter when using string data types otherwise it must be -1 (default value)

Output parameters

When it comes to output parameters, they are used to store values that can be set at any part of the SQL command, they have the same concept of the stored procedure output parameters. As example, if we can use a similar SQL command to store the Maximum value of BusinessEntityID column:

Then we have to configure the parameter mapping as shown in the image below:

This image shows how an output parameter is configured in Execute SQL Task in SSIS

Figure 3 – Output Parameter example

If we add a breakpoint on the PostExecute event of the Execute SQL Task, and we check the variable value, it shows the Maximum BusinessEntityID value as shown below:

This image shows the value of the output parameter configure in Execute SQL Task

Figure 4 – Output Parameter Value

In addition, the output parameter can be defined in a stored procedure as the following:

You can refer to the Microsoft official documentation to learn more about Execute SQL Task in SSIS parameters and return values.

Result Sets

When using an Execute SQL Task in SSIS, result sets can be generated from an SQL command mostly a SELECT query. There are many types of result sets:

  • None: No result set is generated
  • Single Row: When the result set is a single row, such as SELECT TOP 1 or a SELECT MAX() commands
  • Full Result set: When the SQL statement generates multiple rows such as a SELECT * command
  • XML: This option is used to store the result within an XML value

You can select the result set type from the Execute SQL Task editor (General Tab):

This image shows where to change the Result Set type in Execute SQL Task in SSIS

Figure 5 – Result Set type selection

To store the result set into a variable, we must configure the variable mapping within the Result Set tab.

This image shows the Result Set Tab in Execute SQL Task in SSIS

Figure 6 – Result Set Tab

In the Result Set tab, you must specify the result name and the variable that you want to map to. If the result set type is Single row, the result name must be the column name or the column position in the column list. If the result set type is Full result set or XML, you must use 0 as the result set name.

When it comes to variable mapping each type of result set must be mapped to specific variable data types:

  • Single Row: The variable data type depends on the returned column data type
  • Full Result Set: The variable must be a System.Object variable and the Object type is an ADO Recordset mostly or a System.Data.Dataset in case of ADO.NET connection
  • XML: The variable must be a System.String variable where the result will be stored as an XML string, or a System.Object variable where the result set is stored as MSXML6.IXMLDOMDocument mostly or System.Xml.XmlDocument when using an ADO.NET connection

When using Single Row result set or XML string, values are stored within variable and can be consumed directly within Expressions, Tasks, Scripts or Transformations. But, when the result set is stored within a variable of type System.Object, the variable can be consumed using:

  1. ADO enumerator within a Foreach Loop container: This option is used to loop over the rows of the result set and consume them row by row by mapping each row columns with SSIS variables
  2. Using a .Net Script (Task / Component): The code differs based on the Object type:

    1. System.Data.DataSet:

    2. ADO RecordSet:

    3. System.Xml.XmlDocument:

Note that, an ADO RecordSet variable can be consumed only one time.

For additional information you can refer to the official documentations:

Output Parameters Vs Result Sets

Many times, I was asked on the differences between using output parameters and Result Set and which is more recommended when using Execute SQL Task in SSIS. In general, each one of these options has its own use cases even if there are some similarities. In this section, I will try to illustrate the differences and similarities between these two options.

  • When the result consists of multiple rows, the output parameter cannot, since they don’t allow to store a table valued results
  • If we need to store values from different SQL command we cannot use Result Sets, while output parameters can do the trick:

  • In case that we need to retrieve a value from a query located in the middle of the whole SQL statement and reuse this value within the same SQL statement, we need output parameters:

  • Result Set cannot store a Return Value
  • Both options can store values from a SQL command the returns a single row:
    • Result Set:

    • Parameters:

  • Both options can store XML results (To use parameters you must use FOR XML clause in the SQL Ssatement)

  • There are many cases where output parameters and result sets are used in the same Execute SQL Task in SSIS:

  • From a performance perspective, there is a difference between using parameters and Result Set, since in general returning scalar values is more efficient then using Result Set since the second carries a lot of helper methods

External Links and References

Table of contents

SSIS OLE DB Source: SQL Command vs Table or View
SSIS Expression Tasks vs Evaluating variables as expressions
SSIS OLE DB Destination vs SQL Server Destination
Execute SQL Task in SSIS: SqlStatementSource expression vs Variable Source Type
Execute SQL Tasks in SSIS: Output Parameters vs Result Sets
SSIS Derived Columns with Multiple Expressions vs Multiple Transformations
SSIS Data types: Change from the Advanced Editor vs Data Conversion Transformations
SSIS Connection Managers: OLE DB vs ODBC vs ADO.NET

Hadi Fadlallah

Hadi Fadlallah

Hadi is a Lebanese Researcher, Data Engineer and Business Intelligence Developer.

He has been working with SQL Server for more than 10 years. Also, he's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com

Hadi really enjoys learning new things everyday and sharing his knowledge.

View all posts by Hadi Fadlallah
Hadi Fadlallah
1,644 Views