First published on MSDN on Nov 08, 2006
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.
