How to use OUTPUT parameters with SSIS Execute SQL Task

Yesterday while trying to get OUTPUT parameters to work with SSIS Execute SQL Task I encountered a lot of problems, which I'm sure other people have experienced. BOL Help is very light on this subject, so consider this the lost page in help.
The problem comes about because different providers expect parameters to be declared in different ways. OLEDB expects parameters to be marked in the SQL statement with ? (a question mark) and use ordinal positions (0, 1, 2...) as the Parameter name. ADO.Net expects you to use the parameter name in both the SQL statement and the Parameters page.
In order to use OUTPUT parameters to return values, you must follow these steps while configuring the Execute SQL Task:

For OLEDB Connection Types:

1. You must select the OLEDB connection type.
2. The IsQueryStoredProcedure option will be greyed out.
3. Use the syntax EXEC ? = dbo.StoredProcedureName ? OUTPUT, ? OUTPUT, ? OUTPUT, ? OUTPUT The first ? will give the return code. You can use the syntax EXEC dbo.StoredProcedureName ? OUTPUT, ? OUTPUT, ? OUTPUT, ? OUTPUT to not capture the return code.
4. Ensure a compatible data type is selected for each Parameter in the Parameters page.
5. Set your parameters Direction to Output.
6. Set the Parameter Name to the parameter marker's ordinal position. That is the first ? maps to Parameter Name 0. The second ? maps to Parameter Name 1, etc.

For ADO.Net Connection Types:

1. You must select the ADO.Net connection type.
2. You must set IsQueryStoredProcedure to True.
3. Put only the stored procedure's name in SQLStatement.
4. Ensure the data type for each parameter in Parameter Mappings matches the data type you declared the variable as in your SSIS package.
5. Set your parameters Direction to Output.
6. Set the Parameter Name to the same name as the parameter is declared in stored procedure.

For other connection types, check out the table on this page



Note: if you choose the ADO/ADO.Net connection type, parameters will not have datatypes like LONG, ULONG, etc. The datatypes will change to Int32, etc. Make sure that the datatype is EXACTLY the same type as the Variable in your package is defined. If you choose a different datatype (bigger/smaller/different type) you will get the error:

Error: 0xC001F009 at Customers: The type of the value being assigned to variable "User::Result_CustomerID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Add New Customer, Execute SQL Task: Executing the query "dbo.AddCustomer" failed with the following error: "The type of the value being assigned to variable "User::Result_CustomerID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

To fix this error make sure the datatype you select for each parameter in the Parameters page exactly matches the datatype for the variable.

If you have attempted to use a connection type other than ADO.Net with named parameters you will recieve this error:

Error: 0xC002F210 at Add New customer, Execute SQL Task: Executing the query "exec dbo.AddCustomer" failed with the following error: "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Named parameters can only be used with the ADO.net connection type. Use ordinal position numbering in order to use OUTPUT parameters with the OLEDB connection type. Eg: 0, 1, 2, 3, etc.


OUTPUT parameters are extremely useful for returning small fragments of data from SQL Server, instead of having a recordset returned. You might use OUTPUT parameters when you want to load a value into a SSIS Package variable so that the value can be reused in many places. The data that is output might be used for configuring / controlling other Control Flow items, instead of being part of a data flow task.
If you were using output parameters in Management Studio, your SQL statement might look something like:

DECLARE @Name       nvarchar(125)
DECLARE @DOB        smalldatetime
DECLARE @CustomerID int
EXEC dbo.AddCustomer @CustomerName = @Name, @CustomerDOB = @DOB, @CustomerID = @CustomerID OUTPUT
PRINT @CustomerID

If you attempt to use the same syntax (highlighted above) with an Execute SQL Task you could end up with the error message:

Error: 0xC002F210 at Add New customer, Execute SQL Task: Executing the query "EXEC dbo.AddCustomer @CustomerName = @Name" failed with the following error: "Must declare the scalar variable "@Name".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The only hint SQL Server 2005 Books Online gives is:

QueryIsStoredProcedure
Indicates whether the specified SQL statement to be run is a stored procedure. This property is read/write only if the task uses the ADO connection manager. Otherwise the property is read-only and its value is false.

(from SSIS Designer F1 Help > Task Properties UI Reference > Execute SQL Task Editor (General Page) )

There's a number of pages in Books Online that address Parameter use with the Execute SQL Task, but none adaquately address using output parameters. Articles which could do with updating:

• How to: Map Query Parameters to Variables in an Execute SQL Task
• Execute SQL Task Editor (Parameter Mapping Page)
• Execute SQL Task Editor (General Page)
• Execute SQL Task
• Execute SQL Task (Integration Services)