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
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.
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.
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").
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
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):