How to use OUTPUT parameters with SSIS Execute SQL Task

Integration Services 2005 / SSIS (DTS)

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)



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






How to use OUTPUT parameters with SSIS Execute SQL Task | 5 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.
How to use OUTPUT parameters with SSIS Execute SQL Task
Authored by: Anonymous on Friday, September 08 2006 @ 06:57 PM EST

Hi,

I'm trying to use OUTPUT parameters but not obtain success!

I read those posts in: http://p2p.wrox.com/topic.asp?TOPIC_ID=39823

To resolve this problem!

PS. Good Blog Julian! :)p Sorry my english is dirty

André Rentes - Brazil

[ # ]
How to use OUTPUT parameters with SSIS Execute SQL Task
Authored by: Patrick McBurney on Sunday, January 21 2007 @ 02:08 AM EST

I am getting this error but not directly related to parameters.

I have an Execute-SQL-Task against and OLEDB connection to a SQL2000 database and returning the entire result set.

I then use the result set in a ForEach loop and map my variables. One of my fields is defined as VarChar(10) in SQL2000 (with nulls allowed) and it gets mapped to a user variable defined as type String. This works great when there is a value in the field, but when the value is null I get the 0xC001F009 error.

Any Ideas?

[ # ]
How to use OUTPUT parameters with SSIS Execute SQL Task
Authored by: qjadoun on Sunday, January 21 2007 @ 10:24 PM EST

What About using other databases stored procedures such as oracle stored procedures or packages . how can we use output parameters ? please I need help ?

[ # ]
How to use OUTPUT parameters with SSIS Execute SQL Task
Authored by: Deepa on Saturday, May 07 2011 @ 02:42 AM EST

Amazing Article !! Helped me fix my issue in a second. Cant thank enough !!!

[ # ]
How to use OUTPUT parameters with SSIS Execute SQL Task
Authored by: Anonymous on Thursday, September 20 2012 @ 02:43 AM EST

Thank you.  It would have taken me hours to figure this out.

[ # ]


Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 
     
 Copyright © 2014 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.