SSIS - 带输出参数的存储过程 - 黑头

在使用Execute SQL Task控件执行存储过程的时候,SSIS把语句原封不动地传给provider, 结果就导致不同的 provider 需要不同的输入处理方式,这样就显得有点小混乱.这里总结了一些使用不同provider时调用带输出参数的存储过程是遇到的问题.

以下,我使用一个简单的带一个输出参数的存储过程来举例.

CREATE PROC intoutput
@value INT OUTPUT
AS
SELECT @value = 10

我想让Execute SQL Task 运行存储过程,并保存运行结果到包变量(varInt). 对不同的provider,你需要在Parameter Mapping 页面修改 SQLStatement 和 Paramenter Name字段的值.

OLE DB

SQL Statement: exec intoutput ? output
Parameter Name: 0

另外,需要设 BypassPrepare 为 True, 因为如果语句包含参数标记的话 SQL 将不能正确解析

1 – OLE DB General

2 – OLE DB Parameter Mapping

SQL Statement: {call intoutput (?)}
Parameter Name: 1

ODBC 要求的语句和其它的provider 很不一样,你需要使用 "call" 而不是 "exec", 然后用大括号{}把整个语句括起来,而且要注意参数序号从1开始

3 - ODBC General

4 - ODBC Parameter Mapping

SQL Statement: intoutput
Parameter Name:<parameter name>

ADO 只需要使用存储过程名, 并设 IsQueryStoredProcedure 属性为 true. 参数使用存储过程定义使用的参数名 (本例为 value)

5 - ADO General

6 - ADO Parameter Mapping

SQL Statement: intoutput
Parameter Name:@<parameter name>

ADO.NET 和 ADO类似. 设 IsQueryStoredProcedure 为 True, Statement 设为要执行的存储过程名. 用存储过程定义使用的参数名做为Parameter 列的值但是要带前缀@(本例为 "@value").

注意: Parameter mapping 貌似没有@也同样管用,但是我不确定是不是对所有provider都管用

7 - ADO.NET General

8 - ADO.NET Parameter Mapping

总结

用Execute SQL Task执行存储过程因不同的provider处理语句的方式不同而变得不易操作,主要的区别在于SQLStatement参数和Paramenter Name 的值,下表总结了它们的区别 (假设名为'StoreProc'的存储过程带一个叫Para的输出参数):

Provider	SQL Statement	Parameter Name	Notes
OLE DB	exec StoredProc ? output	0	Set BypassPrepare to True
ODBC	{call StoredProc (?)}	1
ADO	StoredProc	Param	Set IsQueryStoredProcedure to True
ADO.NET	StoredProc	@Param	Set IsQueryStoredProcedure to True

For more info about the Execute SQL Task, you can check out the books online entry, and the nice overview of the task on SQLIS.com.

SSIS - 带输出参数的存储过程