Thursday, December 14, 2017

SSIS - Stored Procedures with Return Values

I am finally making a blog post about this. Everytime I go to use this technique, I always forget the syntax and have to scout around to find it.

The scenario: I want to use an "Execute SQL Task" transformation within SSIS to execute a stored procedure, using an OLE DB connection. Then, I want to capture the return value. My problem is, what is the syntax within the Parmeter Mapping configuration screen? I seem to always forget what to type in as the parameter name. :)

I start out providing the needed information about which stored procedure to execute, using which connection. I am adding in a screenshot below, but generally, the syntax for what I need is exec ? = usp_storedprocedurenamehere. (See screenshot 1). Then, when I go to the Parameter Mapping screen, I select the variable I am capturing the integer into, change the direction to be ReturnValue, and leave the default Data Type as LONG. Here is where I get stumped - The ParameterName needs to be 0 (that is a zero). (See screenshot 2) For Input/Ouput parameters, you can specify the numerical sequence of the parameter, or the parameter name. I will show that in another blog post.

Well, this is a short and sweet post - mostly to give me somewhere I can look when I forget, yet again. :)

Talk to you later!


Screenshot 1
screenshot 1


Screenshot 2
screenshot 2


Powered by mod LCA