Stored procedures with output parameters

Stored procedures with output parameters

Rate This
  • Comments 9

When executing Stored Procedures with the Execute SQL Task, SSIS passes the statement to the underlying provider without parsing it. While efficient, the different ways of handling the input between the various providers can be a little frustrating. I was working on a couple of issues found in SP1, and realized that when dealing with stored procedures with output parameters, each provider wanted the SQL in a different format.

I'll stick with a simple stored procedure example that returns a single value.

CREATE PROC intoutput
@value INT OUTPUT
AS
SELECT @value = 10

I want my Execute SQL Task to run the stored procedure, and store the result in a package variable (varInt). For different providers, you'll need to change two main fields – the SQL Statement, and the Parameter Name value on the Parameter Mapping page.

OLE DB

SQL Statement: exec intoutput ? output
Parameter Name: 0

You should also set BypassPrepare to True, as the SQL can't be properly parsed if it contains a parameter marker.

1 – OLE DB General

2 – OLE DB Parameter Mapping

ODBC

SQL Statement: {call intoutput (?)}
Parameter Name: 1

The SQL statement for ODBC is very different than the others. You need to use "call" instead of "exec", and wrap the whole thing with curly braces. Also, the parameter numbering starts at 1 instead of 0.

3 - ODBC General

4 - ODBC Parameter Mapping

ADO

SQL Statement: intoutput
Parameter Name:<parameter name>

For ADO, you can use the stored procedure name by itself, and set the IsQueryStoredProcedure attribute to True. For parameter name, you use the name you gave the parameter in the stored procedure (in this case, "value").

5 - ADO General

6 - ADO Parameter Mapping

ADO.NET

SQL Statement: intoutput
Parameter Name:@<parameter name>

ADO.NET is similar to ADO. Set IsQueryStoredProcedure to True, and set your statement to the name of the SP you want to execute. Like ADO, you need to use the name of the parameter in the stored procedure for the Parameter Name column, prefixed with @ (in this case, "@value"). Note: The parameter mapping seems to work without the use of the @ sign, but I'm not sure if that works for all providers.

7 - ADO.NET General

8 - ADO.NET Parameter Mapping

Summary

Running stored procedures with the Execute SQL Task is tricky due to the differences in the way the providers handle the input SQL. The main differences are with the SQL Statement, and the Parameter Name value. The following table summarizes the differences (assumes a stored procedure of name StoredProc with a single output parameter named Param):

Provider SQL Statement Parameter Name Notes
OLE DB  exec StoredProc ? output  0  Set BypassPrepare to True
ODBC  {call StoredProc (?)}  1  
ADO  StoredProc  Param  Set IsQueryStoredProcedure to True
ADO.NET  StoredProc  @Param  Set IsQueryStoredProcedure to True

For more info about the Execute SQL Task, you can check out the books online entry, and the nice overview of the task on SQLIS.com.

Leave a Comment
  • Please add 6 and 1 and type the answer here:
  • Post
  • PingBack from http://fixmycrediteasily.info/story.php?id=17135

  • There is also a CodePlex sample that demonstrates how to configure the Execute SQL Task, at http://msftisprodsamples.codeplex.com/Wiki/View.aspx?title=SS2008%21ExecuteSQLParametersResultSets%20Sample

  • Hi, can anybody tell me how to iterate through ALL User defined package variables in the script task without knowing actual variable names?

    Thanks.

  • Hi just came across this...not sure if you still need it but you can use the variable enumerator to iterate through all the variables in the package using the MoveNext() method.

    VariableEnumerator e = Dts.Variables.GetEnumerator();

               while (e.MoveNext())

               {

                   //your code here

                }

  • I am just using a regular ADO.NET datasource in control flow and would like to know what's the correct format for using paramaeters???

  • Great documentation above however appears to no longer be valid in 2008R2. What you left out was what datatype for the variable you are using which is critical. I tried to replicate the above for both oledb and ado.net and neither work in 2008R2 using every variable datatype I could. Character parameters work great - never a problem. But can't pass any numeric or integer. Tried INT in sproc with all the various forms of variable datatypes. INT32 runs without error but returns 0 for a value. All the other combinations result in error in SSIS.

  • Great doc - works like a charm!!! Even on SQL08R2.(Gary, have to pay attention, var types are fully defined ;)

  • Matt,

    Thanks very much for the SSIS Blog.

    I'll tell you this figuring out how to set SSIS task variables and map them with the different formats for conneciton types is maddening. Your blog was spot on. I am trying to kick off an SSIS package from a C#.NET method in a web app which calls a stored procedure which has a try catch for each of my Oracle

    to SQL load for 21 tables. Trying to figure out the best way to dump my error message to our log in SQL

    Server while returning a simple request success or failure indicator of 'Y' or 'N'. The trick is call into the

    layered tasks and procedure and have a way to return values that I can use to control execution and

    take reporting and restore steps if a problem occurs.

    Have a good day.

    Brian

  • All of the pictures in this post no longer work.  Any chance on getting them fixed?

Page 1 of 1 (9 items)