OLE DB Command and Stored Procedure that returns value and/or error

Guys,
could someone please tell me : am I supposed to use the OLE DB Command in a dataflow to call a stored procedure to return a value? Or is it just supposed to be used to call a straightforward insert statement only?
What I am hoping to do:
I have a table with a few columns and one identity column. In a dataflow I would like to effect an insert of a record to this table and retrieve the identity value of the inserted record... and I'd like to store the returned identity in a user variable.
If I AM supposed to be able to do this... then how on earth do I do it?
I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.
In the Advanced Editor any time I try to add an output column (by clicking on Add Column) I just get an error dialog that says "the component does not allow adding columns to this input or output)
So, am getting pretty concussed .. banging my head of the wall like this...
So put me out of my misery someone please.... is the OLE DB Command intended for this or not?
Thanks
PJ

I'm not terribly au fait with the OLE DB Command other than using it for UPDATEs but I do know that you can execute a stored procedure that uses values from the pipeline as parameters.

I suspect that you cannot get return values from the sproc and add that returned value into the pipieline - I stand to be corrected though. I have never tried it.

-Jamie

|||

PJ,

You can probably look at this post..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=490010&SiteID=1

You can pass back return value into the pipe and use it in a derived column.

|||

PJ,

I do not know how many rows are you planning to insert every time; but trying to capture the identity value at the dataflow sounds a kind of expensive from performance standpoitn. Have you consider to generate your own surrogatekey in a script task instead?

Rafael Salas

|||

Hi there Rafael,

Yeah, I suppose it could get pretty slow if you have a lot of data.. I only have ten small files (10 rows each) to load.. so its not a huge performance hit.

An I already have a DAL written for other applications to call stored procs's and was hoping to use that....

Its a while ago that I wrote the previous post.. and solved the problem in the meantime by writing a script task and calling the stored proc from inside that...

It's an ok approach for me (using small amounts of data) but I remember it seemed like a lot of work to do something I think should be easy to do...

Thanks

PJ