Execute SQL Task 参数和变量的映射

首页 > 代码库 > Execute SQL Task 参数和变量的映射

Execute SQL Task 参数和变量的映射

2024-08-15 23:34:28 220人阅读

Execute SQL Task能够执行带参数的SQL查询语句或存储过程（SP），通过SSIS的变量（Variable）对参数赋值。对于不同的Connection Manager，在Task中需要使用不同的符号（Parameter marker）来标记一个参数，并且在Parameter Mapping中设置参数名字（Parameter Name）。

在Execute SQL Task Editor中，设置Parameter Mapping的界面如下：

Variable Name：变量的名字，变量的作用域分为User和System，使用 :: 来引用作用域中的变量
Direction：参数的方向，分为输入参数（Input），输出参数（Output）和返回值（Returnvalue）
Data Type：参数的Data Type，必须和变量（Variable）的数据类型相兼容
Parameter Name：参数名字，不同的Connection Manager，其值不同，对于OLEDB Connection Manager，使用0，1，2等数值表示第一个，第二个参数，第三个参数等。
Parameter Size：默认值是-1，表示让SSIS确定参数的长度。如果参数的数据类型是变长的（varchar或varbinary），必须设置参数的长度，为参数值分配足够长度的空间。

技术分享

在Parameter Mapping中，Parameter Marker和Name受到Connection Type的影响，如下表：

Connection type	Parameter marker	Parameter name	Example SQL command
ADO	?	Param1, Param2, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
ADO.NET	@<parameter name>	@<parameter name>	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID
ODBC	?	1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
EXCEL and OLE DB	?	0, 1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

1，执行SQL查询语句

楼主在实际开发中，最常用的Connection Type是OLEDB，设置Task的ConnectionType为OLE DB，在SQLStatement属性中使用 ? 来标记一个参数，? 叫做参数标记（Parameter Marker）。

select ID,c1from dbo.dt_testwhere c1=?

技术分享

使用SSIS 变量（Variable）为参数赋值，Parameter Name必须是0，1，2等数字

技术分享

2，执行SP，设置参数的方向

使用Execute SQL Task执行SP时，参数映射的Direction属性使用Input，Output和Returnvalue 分别表示：输入参数，输出参数和SP的返回值

declare @return_value int declare @output_var int declare @input_var intset @input_var=1
exec @return_value=dbo.usp_test @para1=@input_var, @para2= @output_var output;