Post reply

Error Assigning Variable to ResultSet of a TeraData Procedure inside SSIS 2012. Exception from HRESULT: 0xC0015005

  • March 23, 2017 at 5:58 am

    #328887

    Hi SSIS Folks,

    I am trying to execute a stored procedure from a execute SQL task in SSIS connecting to TeraData Database. The stored procedure returns a date (typecasted as varchar in the procedure) from a table where it connects to.

    To make it simple, I am passing a hard coded value instead of passing parameters, I need to capture this resultset in a variable 'BDM' of String type.

    So my SQL Statement configuration is like this.

    GENERAL
    Result Set                :   Single Row
    Connection Type     :   OLEDB
    SQL Source Type    :   Direct input
    SQL Statement        :   CALL DB.p_procedure_name ('S01',O)

    PARAMETER MAPPING
    nothing

    RESULT SET
    p_out User::BDM    (scope of variable is at package level)

    ERROR MESSAGE

    [Execute SQL Task] Error: An error occurred while assigning a value to variable "BDM": "Exception from HRESULT: 0xC0015005".

    Needless to say, when I execute below from Teradata SQL Assistant

    CALL DB.p_procedure_name ('S01',O)

    I get below and procedure looks fine to me. 

    p_out
    2017-03-22

    When using SQL Server Database this is a standard operation wherein we assign values captured from a SQL Statement/procedure and assigning it to variable. Not sure why this is giving error in Teradata.
    From error message its obvious that error is while assigning result of procedure to the Variable BDM.
    if I choose resultset to NONE then Execute SQL Task runs successfully, indicating that the compilation of procedure is correct. (Although to use this result I have to use ResultSet to SingleRow and assign this to a variable so I can use it afterwards and that's where I am getting error)

    ERROR Message is attached and seems like SSIS is assuming that the procedure call will not return any value and I have specified ResultSet to SingleRow which is very much what I want.

  • March 24, 2017 at 1:34 am

    #1934693

    Hi,

    You need to make sure you are returning something in your stored procedure.
    Please see the following link for an example, under "Single Result Set".

  • March 24, 2017 at 9:18 am

    #1934792

    Dates. Dates are the bane of working between database systems.

    Try setting your variable type String and see what happens. You can always cast it as a date later.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply