Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS- Call to Stored Procedure Expand / Collapse
Author
Message
Posted Friday, March 29, 2013 5:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:28 AM
Points: 36, Visits: 121
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 .

Post #1436881
Posted Monday, April 1, 2013 11:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 7,080, Visits: 12,571
greeshatu (3/29/2013)
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 .


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
Post #1437547
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse