Need sol for storing sp return value into a variable

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

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

@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

Answer 1

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

Answer 2

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