ssis sql_在SSIS中执行SQL任务：输出参数与结果集

ssis sql

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.

在上一篇文章中，我对SSIS中的Execute SQL Task进行了概述，并说明了编写表达式以评估SqlStatementSource属性与在变量中编写此表达式并将Execute SQL Task Source Type更改为变量之间的一些区别。

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

在本文中，我将描述在SSIS的Execute SQL Task中使用输出参数和结果集之间的区别。

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.

请注意，本文是SSIS功能面对面系列文章中的第五篇，该文章旨在消除混淆并说明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.

SSIS中的执行SQL任务允许用户执行参数化SQL语句并在这些参数和SSIS变量之间创建映射。 要将参数添加到SQL语句中，必须使用根据连接类型而不同的参数标记。

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 > ?

连接类型	记号笔	例
阿多	？	从ID>？的表中选择*。
ADO.NET	@ <参数名称>	从ID> @ID的表中选择*
SQL移动	@ <参数名称>	从ID> @ID的表中选择*
OLEDB	？	从ID>？的表中选择*。
ODBC	？	从ID>？的表中选择*。
卓越	？	从ID>？的表中选择*。

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

SSIS中的“执行SQL任务”中可以使用三种类型的参数：

1. Input parameters: used to pass a value as a parameter within a SQL command or stored procedure 输入参数：用于在SQL命令或存储过程中将值作为参数传递
2. Output parameters: used to store a value generated from an SQL command or stored procedure 输出参数：用于存储从SQL命令或存储过程生成的值
3. Return Value: used to store a value returned by an SQL command or stored procedure 返回值：用于存储由SQL命令或存储过程返回的值

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.

在SSIS中使用“执行SQL任务”时，要将参数映射到变量，必须转到“ 参数映射”选项卡，然后定义变量映射。

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

例如，在执行SQL任务中，我们定义了以下SQL命令：

SELECT      *
FROM          AdventureWorks2017.Sales.SalesPerson
WHERE        (ModifiedDate >= ?)

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

现在，如果单击“参数映射”选项卡，则应该看到以下形式：

Figure 1 – Parameter Mapping Tab

图1 –参数映射选项卡

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:

要添加参数映射，我们必须单击“ 添加”按钮，因为我们在SQL命令中指定了一个参数，所以仅应添加一个映射。 当我们单击“ 添加”按钮时，一行将添加到网格中，如下图所示：

Figure 2 – Adding Parameter Mapping

图2 –添加参数映射

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

在SSIS中的“执行SQL任务”中，您必须配置每个参数的以下属性：

• 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, …

  连接类型	参数名称
  阿多	参数1，参数2 ...
  ADO.NET和SQLMOBILE	@ <参数名称>
  ODBC	1 2 3
  EXCEL和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)
• 参数大小：使用字符串数据类型时，请指定参数的长度，否则必须为-1（默认值）

输出参数 (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:

对于输出参数，它们用于存储可以在SQL命令的任何部分设置的值，它们与存储过程输出参数的概念相同。 例如，如果我们可以使用类似SQL命令来存储BusinessEntityID列的Maximum值：

SELECT ? = MAX(BusinessEntityID)
FROM AdventureWorks2017.Sales.SalesPerson

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

然后，我们必须配置参数映射，如下图所示：

Figure 3 – Output Parameter example

图3 –输出参数示例

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:

如果我们在Execute SQL Task的PostExecute事件上添加一个断点，并检查变量值，它将显示Maximum BusinessEntityID值，如下所示：

Figure 4 – Output Parameter Value

图4 –输出参数值

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

此外，可以在存储过程中按以下方式定义输出参数：

EXEC GetMaxEntityID ? OUTPUT

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

您可以参考Microsoft官方文档以了解有关SSIS参数和返回值中的“执行SQL任务”的更多信息。

结果集 (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:

在SSIS中使用“执行SQL任务”时，可以从SQL命令（主要是SELECT查询）生成结果集。 结果集有很多类型：

• 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 单行：当结果集为单行时，例如SELECT TOP 1或SELECT MAX（）命令
• Full Result set: When the SQL statement generates multiple rows such as a SELECT * command 完整结果集：当SQL语句生成多行时（例如SELECT *命令）
• XML: This option is used to store the result within an XML value XML：此选项用于将结果存储在XML值内

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

您可以从“执行SQL任务”编辑器（“常规”选项卡）中选择结果集类型：

Figure 5 – Result Set type selection

图5 –结果集类型选择

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

要将结果集存储到变量中，我们必须在“ 结果集”选项卡中配置变量映射。

Figure 6 – Result Set Tab

图6 –结果集选项卡

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.

在“结果集”选项卡中，必须指定结果名称和要映射到的变量。 如果结果集类型为Single row ，那么结果名称必须是列名称或列列表中的列位置。 如果结果集类型为完整结果集或XML，则必须使用0作为结果集名称。

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
  单行：变量数据类型取决于返回的列数据类型
• System.Object variable and the Object type is an System.Object变量，并且对象类型主要是ADO Recordset mostly or a ADO Recordset ，如果是ADO.NET连接，则对象是System.Data.Dataset in case of ADO.NET connection System.Data.Dataset
• System.String variable where the result will be stored as an XML string, or a System.String变量，或者是使用ADO时将结果集主要存储为System.Object variable where the result set is stored as MSXML6.IXMLDOMDocument或MSXML6.IXMLDOMDocument mostly or System.Xml.XmlDocument的System.Xml.XmlDocument when using an ADO.NET connection System.Object变量。 NET连接

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:

使用单行结果集或XML字符串时，值存储在变量中，并且可以直接在表达式，任务，脚本或转换中使用。 但是，当结果集存储在类型为System.Object的变量中时，可以使用以下方式使用该变量：

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 Foreach循环容器中的ADO枚举器 ：此选项用于循环遍历结果集的行，并通过将每个行列与SSIS变量映射来逐行使用它们

2. Using a .Net Script (Task / Component): The code differs based on the Object type:

   使用.Net脚本（任务/组件）：代码因对象类型而异：

   1. System.Data.DataSet:

      System.Data.DataSet：

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

   2. ADO RecordSet:

      ADO RecordSet：

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

   3. System.Xml.XmlDocument:

      System.Xml.XmlDocument：

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

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

请注意，ADO RecordSet变量只能使用一次。

For additional information you can refer to the official documentations:

有关其他信息，请参阅官方文档：

• Result Sets in the Execute SQL Task 执行SQL任务中的结果集
• Execute SQL Task Editor (Result Set Page) 执行SQL任务编辑器（结果集页面）
• Execute SQL Task in SSIS 在SSIS中执行SQL任务

输出参数与结果集 (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.

很多时候，有人问我使用输出参数和结果集之间的区别，在SSIS中使用“执行SQL任务”时，更建议这样做。 通常，即使存在一些相似之处，这些选项中的每一个都有自己的用例。 在本节中，我将尝试说明这两个选项之间的区别和相似之处。

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

  如果需要存储来自不同SQL命令的值，则不能使用结果集，而输出参数可以解决问题：

  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:

  如果我们需要从位于整个SQL语句中间的查询中检索一个值并在同一SQL语句中重用此值，则需要输出参数：

  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
  结果集无法存储返回值

• • Result Set:

    结果集：

    SELECT TOP 1 ID ,NAME FROM Employees
    

  • Parameters:

    参数：

    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)

  这两个选项都可以存储XML结果（要使用参数，必须在SQL Ssatement中使用FOR XML子句）

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

  在许多情况下，在SSIS中的同一Execute SQL Task中使用输出参数和结果集：

  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 将查询参数映射到执行SQL任务中的变量
• Execute SQL Task output parameter vs ResultSet 执行SQL Task输出参数与ResultSet

目录 (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 Expressions vs Variable Source Types

Execute SQL Task 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

SSIS Flat Files vs Raw Files

SSIS Foreach Loop vs For Loop Container

SSIS: Execute T-SQL Statement Task vs Execute SQL Task

SSIS OLE DB来源：SQL命令与表或视图

SSIS表达式任务与将变量作为表达式求值

SSIS OLE DB目标与SQL Server目标

在SSIS中执行SQL任务：SqlStatementSource表达式与可变源类型

在SSIS中执行SQL任务：输出参数与结果集

具有多个表达式与多个转换的SSIS派生列

SSIS数据类型：高级编辑器的更改与数据转换的转换

SSIS连接管理器：OLE DB与ODBC与ADO.NET

SSIS平面文件与原始文件

SSIS Foreach循环与For循环容器

SSIS：执行T-SQL语句任务与执行SQL任务

翻译自: https://www.sqlshack.com/execute-sql-tasks-in-ssis-output-parameters-vs-result-sets/

ssis sql