无法将变量值传递给ssis中的存储过程(Unable to pass a variable value to a stored procedure in ssis)

无法将变量值传递给ssis中的存储过程(Unable to pass a variable value to a stored procedure in ssis)

执行SSIS包时，会出现以下错误：

[OLE DB Source [83]]错误：SQL命令需要名为“@Sales_person”的参数，该参数在参数映射中找不到。

[SSIS.Pipeline]错误：OLE DB源未执行预执行阶段并返回错误代码0xC0207014。

下面是我的OLE DB源编辑器的屏幕截图

我确实在Set Query参数中看到Param方向选项卡，该怎么用？ 在我的情况下，我将使用Input或Output或InputOutput

When executing an SSIS package, the following error appears:

[OLE DB Source [83]] Error: The SQL command requires a parameter named "@Sales_person", which is not found in the parameter mapping.

[SSIS.Pipeline] Error: OLE DB Source failed the pre-execute phase and returned error code 0xC0207014.

Below is the screenshot of my OLE DB Source editor

I do see Param direction tab in Set Query parameters, how is that used? In my case will I be using Input or Output or InputOutput

最满意答案

搜索后，我没有找到适合这个问题的解决方案。 很多建议，比如在执行命令之前添加SET NOCOUNT ON 。 下面是一些相关链接：

http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspx http://www.sqlservercentral.com/articles/Data+Flow+Task+(SSIS)/117370/ http://www.ssistalk.com/2007/10/10/ssis-stored-procedures-and-the-ole-db-source/

你可以做一个解决方法

声明一个SSIS变量（假设@[User::Query] ）

设置@[User::Query]属性EvaluateAsExpression = True并使用以下表达式

"EXEC [dbo].[GetDales_Person_data] " + @[User::Sales]

如果@[User::Sales]是一个字符串，请使用以下命令

"EXEC [dbo].[GetDales_Person_data] '" + @[User::Sales] + "'"

然后在OLEDB Source使用SQL Command from variable选择@[User::Query]

After searching i didn't find a solution for this issue that worked for me. Ther are many suggestions like adding SET NOCOUNT ON before the execute command. Below some related links:

http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspx http://www.sqlservercentral.com/articles/Data+Flow+Task+(SSIS)/117370/ http://www.ssistalk.com/2007/10/10/ssis-stored-procedures-and-the-ole-db-source/

You can do a workaround

Declare a SSIS variable (assuming @[User::Query])

Set @[User::Query] property EvaluateAsExpression = True and use the following expression

"EXEC [dbo].[GetDales_Person_data] " + @[User::Sales]

if @[User::Sales] is a string use the following

"EXEC [dbo].[GetDales_Person_data] '" + @[User::Sales] + "'"

Then In OLEDB Source use SQL Command from variable and select @[User::Query]