ssis中的错误执行sql任务

I have placed a execute sql task inside the foreach loop container , where i want to execute the stored procedure name obtaining from the ssis user defined variable But i am not finding a right syntax to execute stored procedure name from variable. and i want to pass input parameter to execute the stored procedure which is also a another dts variable Please can any one help to figure out the right syntax

我在foreach循环容器中放置了一个执行sql任务，我想执行从ssis用户定义的变量获取的存储过程名称但是我没有找到一个正确的语法来从变量执行存储过程名称。我想传递输入参数来执行存储过程，这也是另一个dts变量请任何人都可以帮助找出正确的语法

2 个解决方案

#1

Lets say you have two variables StoredProcedureName and ParameterValue

假设您有两个变量StoredProcedureName和ParameterValue

Create a new variable called QueryString. Open up the properties window by pressing F4 and then click on the variable name in the window that has list of variables. Set the EvaluateAsExpression property of your QueryString variable to True.

创建一个名为QueryString的新变量。按F4打开属性窗口，然后在包含变量列表的窗口中单击变量名称。将QueryString变量的EvaluateAsExpression属性设置为True。

Then click the elliptical in Expression to open up the Expression Builder. Type the following expression

然后单击Expression中的椭圆以打开表达式生成器。键入以下表达式

"Execute " + @[User::StoredProcedureName] + " @ParameterName = '" + @[User::ParameterValue]+ "'"

Click on Evaluate Expression and you should see

点击评估表达式，您应该看到

Execute MyProcedure @ParameterName = 'SomeValue'

执行MyProcedure @ParameterName ='SomeValue'

Assign this variable to your SourceVariable for the Execute SQL Task.

将此变量分配给Execute SQL Task的SourceVariable。

#2

You can build your string out as provided above but I would not start with it. For one, it assumes everything is string and second it could conceivably open you up to sql injection (highly improbable, I agree)

你可以像上面提供的那样构建你的字符串，但我不会从它开始。首先，它假设一切都是字符串，其次它可以让你开放sql注入（非常不可能，我同意）

To actually use the native parameter mapping for ADO.NET you need to use the @PlaceHolder for your variable substitution. Based on your screenshot, the value of @[User::StoredProcedureName] would need to have the format of EXECUTE schema.ProcName @ParameterName Otherwise, you're looking at cobbling the expression from @Raj More's example without the =... piece

要实际使用ADO.NET的原生参数映射，您需要使用@PlaceHolder进行变量替换。根据您的屏幕截图，@ [User :: StoredProcedureName]的值需要具有EXECUTE schema.ProcName @ParameterName的格式。否则，您正在考虑从@Raj更多的示例中拼写表达式而不使用= ... piece

Mapping is as simple as the following. Ensure you select the correct Data Types and Parameter Names to correlate with your query.

映射非常简单，如下所示。确保选择正确的数据类型和参数名称以与查询相关联。