locked
Mapping sp output variable to SSIS variable RRS feed

  • Question

  • Hello,

     

    I am calling a stored procedure from an Execute SQL Task:

     

    EXEC myProc @EventId OUTPUT, 'asdf', 'asdf'

     

    I would like to map @EventId to an SSIS package variable using the Result Set functionality of the Execute SQL Task.

     

    Can this be done? So far, not having much luck.

     

    Thank you

    Sunday, June 8, 2008 1:16 AM

Answers

  • Ok - I solved the problem by making User::eventID an "object"

     

    Then I used INPUT/OUTPUT parameter mapping and made all the variables "SHORT"

     

    and that works

     

     

    Sunday, June 8, 2008 2:39 AM

All replies

  • This is the error message:

     

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

     

    eventID is my SSIS variable. It's an INT32.

     

     

     

    Sunday, June 8, 2008 1:20 AM
  • Nevermind... had to set result set to "None" - not "Single Row"

     

     

     

    Sunday, June 8, 2008 1:40 AM
  • arghhh

     

    I can't seem to get this to work from an "Execute SQL" task:

     

    DECLARE @EventID INT
    EXEC myProc @EventID OUTPUT, 'asdf, 'asdf'

     

    I want to capture @EventID in an SSIS variable.

     

    I've tried:

     

    Result set: SINGLE ROW

    and adding a Result Set variable: @EventID, eventID

     

    and get this error:

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

     

    Is this a data type problem?

     

    --------------------------------

     

    I've also tried:

     

    EXEC myProc ? OUTPUT, 'asdf', 'asdf'

     

    Result set: NONE

    Parameter mapping: User::eventID, Output, LARGE_INTEGER, 0

     

    and get this error:

    [Execute SQL Task] Error: Executing the query "EXEC myProc ? OUTPUT, 'asdf', asdf'" failed with the following error: "The type of the value being assigned to variable "User::eventID" 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.


     

    Sunday, June 8, 2008 2:19 AM
  • Ok - I solved the problem by making User::eventID an "object"

     

    Then I used INPUT/OUTPUT parameter mapping and made all the variables "SHORT"

     

    and that works

     

     

    Sunday, June 8, 2008 2:39 AM