Wednesday, February 9, 2011

How to use OUTPUT parameters with SSIS Execute SQL Task

How to use OUTPUT parameters with SSIS Execute SQL Task

Integration Services 2005 / SSIS (DTS) I was 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)

Source:http://www.julian-kuiters.id.au/article.php/ssis-execute-sql-task-output-parameters

    7 comments:

    Anonymous said...

    Attractіѵe pοrtion of content. I just stumbled uρon уоur sitе and in accesѕion caрital tο claim that
    Ι get aсtually enjοyed account your blοg ρosts.
    Αny way І ωill be ѕubѕсrіbing on yοur fеeds
    or eѵen I fulfillment yοu gеt right
    of еntry tο constantly fast.
    Here is my web site flex belt for cheap

    Anonymous said...

    Mу brother suggеsted I might like thіs webѕіtе.
    He wаs entiгely right. This poѕt truly made
    my ԁay. You сann't imagine simply how much time I had spent for this info! Thanks!
    Feel free to surf my web page http://Hotmailcorreo.Webs.com

    Anonymous said...

    I am really grateful to the holder of this website who has shared this enormous paragraph at at this place.


    my blog ... weebly.com

    Anonymous said...

    Тhe cream is totally sеcure аnԁ its uѕeгѕ еndure from
    no siԁe effeсts.

    Hаvе a look at my web site; http://www.prnewswire.com/news-releases/trilastin-review-and-latest-coupon-code-savings-released-at-awesomealldaycom-190256601.html
    My website: http://www.prnewswire.com/news-releases/trilastin-Review-and-latest-coupon-code-savings-released-at-awesomealldaycom-190256601.html

    Anonymous said...

    We absolutely love your blog and find most of
    your post's to be what precisely I'm looking for. Does
    one offer guest writers to write content for you personally?
    I wouldn't mind writing a post or elaborating on some of the subjects you write concerning here. Again, awesome site!

    Also visit my web page ... healthy diet
    Also see my web site: healthy diet plans for women

    Anonymous said...

    I'm really enjoying the design and layout of your blog. It's a very easy on the
    eyes which makes it much more enjoyable for me to come here and visit more often.

    Did you hire out a designer to create your theme? Outstanding work!


    Here is my page: http://rossimasonry.Com/project/334

    Anonymous said...

    Fabulous work. The BOL was no help at all and as usual, of the many different ways of achieving a result you have provided the precise solution.

    Popular Posts