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

问题描述

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

When executing an SSIS package, the following error appears:

[OLE DB源[83]]错误:SQL命令需要一个名为的参数 在参数映射中找不到"@Sales_person".

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

[SSIS.Pipeline]错误:OLE DB源在预执行阶段失败，并且 返回错误代码0xC0207014.

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

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

Below is the screenshot of my OLE DB Source editor

我确实在设置查询"参数中看到参数方向"选项卡，它是如何使用的?就我而言，我将使用Input或Output或InputOutput

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

推荐答案

搜索后，我没有找到适合我的解决方案.还有许多建议，例如在execute命令之前添加SET NOCOUNT ON.下面是一些相关链接:

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/

• 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])

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

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

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

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

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

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

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

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

这篇关于无法将变量值传递给SSIS中的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！