March 29, 2013 at 5:35 am
Hi
I have scenario where I need to Call an Storeprocedure in SQL task
Eg: Procedure Name Like Usp_ABCDEF 'Param1',Param2'
Part of Stored Procedure name is coming from Varible
Eg: @Entity='DEF' [@Entity is a variable for the task]
How to pass it in SQL statement of SQL Task
I have done like EXEC ('Usp_ABC'+? ? ?)
But Iam getting an error .
Kindly request to help me .
April 1, 2013 at 11:37 am
greeshatu (3/29/2013)
HiI have scenario where I need to Call an Storeprocedure in SQL task
Eg: Procedure Name Like Usp_ABCDEF 'Param1',Param2'
Part of Stored Procedure name is coming from Varible
Eg: @Entity='DEF' [@Entity is a variable for the task]
How to pass it in SQL statement of SQL Task
I have done like EXEC ('Usp_ABC'+? ? ?)
But Iam getting an error .
Kindly request to help me .
The ? mark denotes a parameter in your statement which when it comes to executing a SQL statement has a little more meaning than just a simple placeholder for a string substitution. Instead of using parameters you'll need to build your SQL statement in an SSIS Variable using an SSIS Expression and then map that Variable to the SQLStatement property of your Execute SQL Task.
The Expression for your new Variable (named as @NewVariable for discussion purposes) might look like this, where Entity, Param1 and Param2 are also SSIS Variables in your SSIS Package:
"Usp_ABC" + @[User::Entity] + "'" + @[User::Param1] + "','" + @[User::Param2] + "'"
And the Expression for your SQLStatement might look like this:
@[User::NewVariable]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply