error in ssis execute sql task

Question

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

Answer 1

Lets say you have two variables StoredProcedureName and 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.

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

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

Click on Evaluate Expression and you should see

Execute MyProcedure @ParameterName = 'SomeValue'

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

Answer 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)

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

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