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.

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:

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:

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:

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:

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):

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.

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:

      1 2 3

      Dim dt As Data.DataTable Dim ds As Data.DataSet = CType(Dts.Variables("Recordset").Value, DataSet) dt = ds.Tables(0)

   2. ADO RecordSet:

      1 2 3

      Dim dt As DataTable Dim adapter As New OleDbDataAdapter() adapter.Fill(dt, Dts.Variables("User::transactionalRepDBs").Value)

   3. System.Xml.XmlDocument:

      1 2	Dim xdoc As New XmlDocument() xdoc.LoadXml(varCollection("User::XmlVariable").Value.ToString())

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

For additional information you can refer to the official documentations:

• Result Sets in the Execute SQL Task
• Execute SQL Task Editor (Result Set Page)
• Execute SQL Task in SSIS

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:

  1 2 3

  SELECT ? = MAX(ID) FROM Employees GO SELECT ? = MAX(ID) FROM Customers

• 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:

  1 2 3 4 5

  DECLARE @MaxID INT SELECT @MaxID = MAX(ID) FROM Employees SELECT ? = @MAXID INSERT INTO EMPLOYEES(ID, NAME) VALUES (@MAXID,?)

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

  • Result Set:

    1	SELECT TOP 1 ID ,NAME FROM Employees

  • Parameters:

    1	SELECT TOP 1 ? = ID, ? = NAME FROM Employees

• 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:

  1 2 3 4 5 6

  DECLARE @MaxID INT SELECT @MaxID = MAX(ID) FROM Employees SELECT ? = @MAXID INSERT INTO EMPLOYEES(ID, NAME) VALUES (@MAXID,?); SELECT * FROM EMPLOYEES

• 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

• Map Query Parameters to Variables in an Execute SQL Task
• Execute SQL Task output parameter vs ResultSet

Table of contents

• Author
• Recent Posts

Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies.

Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.

On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.

Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.

View all posts by Hadi Fadlallah

Latest posts by Hadi Fadlallah (see all)

• An overview of SQL Server monitoring tools - December 12, 2023
• Different methods for monitoring MongoDB databases - June 14, 2023
• Learn SQL: Insert multiple rows commands - March 6, 2023