locked
Need sol for storing sp return value into a variable RRS feed

  • Question

  • Hiiiii,

      My  package has 3 variables(Src ,Dest and ReturnValue )declared at  package level and all are Int32 data type Type. 

     My sp contains One input parameter and one output parameter. It(sp) is returning a result set and a returnvalue.When i  am executing the sp using Execute Sql Task, I am able to capturing the return value into a variable(i.e.ReturnValue). The following is my query:

    exec ?= dbo.Promotion_TransactionExtract_j6 ?,? OUT

    and Parameter Mapping

    Parameter mapping

    But i am unable to capturing the return value,  when i execute the sp in oledb source under Data Access Mode is SQL Command. I used the following Query:

    exec  dbo.Promotion_TransactionExtract_j6 ?,? OUT

    and Parameter Mapping

    Parameter Mapping in Oledb source

    @LAST_EXECUTED_ID_IN is my input parameter and @LAST_EXECUTED_ID_OUT is my output parameter.

    If i use the following query it is giving error:

    exec ?= dbo.Promotion_TransactionExtract_j6 ?,? OUT

    Error:

    [OLE DB Source [1]] Error: The SQL command requires a parameter named "@RETURN_VALUE", which is not found in the parameter mapping.

    [SSIS.Pipeline] Error: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC0207014.

    But i have only one input parameter and one output parameter so that 

     how can i capture sp return value in this scenario?

    can anyone help on dis?

    Thanks,

    Visu

    Saturday, June 2, 2012 12:28 PM

Answers

All replies

  • I duplicated your setup on SSIS 2012 and get slightly different results.  I do get the opportunity to set up a @RETURN_VALUE parameter in the OLEDB command, but do not get the return value or output parameter set in the 2 variables, they stay at their original values.  My proc is hard coded to return 1 as the return value and test as Parm3 - the dataviewer afterwards shows the defaults I set up of 0 and er on the variables


    Chuck Pedretti | Magenic – North Region | magenic.com


    Saturday, June 2, 2012 1:49 PM
  • And this thread indicates what we are seeing - that the OLEDB source is not capable of actually populating output params.  It does list an alternative in the form of calling the proc in a script transform, that seems like the best course.

    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/d85668b6-2981-4b1c-978d-227483d03bae/


    Chuck Pedretti | Magenic – North Region | magenic.com


    • Edited by Chuck Pedretti Saturday, June 2, 2012 1:55 PM
    • Proposed as answer by Eileen Zhao Monday, June 4, 2012 6:27 AM
    • Marked as answer by Eileen Zhao Friday, June 15, 2012 7:31 AM
    Saturday, June 2, 2012 1:54 PM