how to pass parameter to stored proc from Execute SQL task in SSIS

Question

how to pass parameter to stored proc from Execute SQL task in SSIS

fgacer

SSC Veteran

Points: 257
More actions
April 15, 2009 at 10:11 am

#130121

How to pass parameter to stored proc from Execute SQL task in SSIS ? Please advice

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Dave23 Hall of Fame Points: 3903 More actions · Answer 1

For passing parameters to stored procedures, I use an ADO.NET connection manager, and create a package scope user variable that will hold the value to pass. Get the value into the variable by whatever means you prefer, such as directly setting it, getting it from a XML config file, or via a derived column transformation.

On the "General" section of the Execute SQL Task editor, set the connection type to ADO.NET, the connection to whatever your ADO.NET connection name is, SQLSourceType to "Direct Input", SQLStatement to the name of your stored procedure, and IsQueryStoredProcedure to True.

On the "Parameter Mapping" section of the Execute SQL Task editor, select the variable name that is passing the parameter value, the direction, data type, parameter name (from the proc), and parameter size.

For example, to pass the string "Fred" to the input parameter "@FirstName" in a proc, I would create a string user variable FirstName in the package, then in the task editor, set variable name to User::FirstName, Direction to Input, Data Type to String, parameter to @FirstName, and parameter size to -1.

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 2

Use OLEDB connection managers (much faster than ADO.NET... u dont necessarily have to use ADO.NET).

Map the SSIS variables to parameters of the stored proc based on ordinal positioning of the parameters (In the Name of the parameter start with 0 for 1st param, 1 for 2nd param and so on).

If u get any probs, view this.

http://www.expertsqltraining.com/viewarticle.aspx?id=76

Dave23 Hall of Fame Points: 3903 More actions · Answer 3

For pure data movement tasks, I agree that OLEDB connections are faster, but I have found the ADO.NET connection to be much easier to use for procedure calls. In any package with calls, I add the second connection just for that purpose.

Here is an article that discusses the merits of this approach:

http://davefackler.blogspot.com/2008/02/using-connection-managers-in-ssis.html

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 4

vishal.gamji

SSCertifiable

Points: 6126

April 16, 2009 at 11:36 am

#979086

elegant vs efficient .. i guess elegant wins.

🙂

sunjer777 SSC Journeyman Points: 97 More actions · Answer 5

Dudes, Check the following link. I wasted lot of time but finally found a great great solution...

http://www.tek-tips.com/viewthread.cfm?qid=1247725&page=1

Great work Dan

AmolNaik SSCarpal Tunnel Points: 4767 More actions · Answer 6

Yes, we use expressions to form the SQL statements a lot in our ETL process, it's much easier to implement than using parameters, which is messy at a times and prone to errors.

Best regards,

Amol

Amol Naik