SSIS return value of Stored Procedure within an OLE DB Command

Question

I am migrating data that has to be inserted using stored procedures which already exist. The stored procedures have parameters and a return value (from a select statement) of an id for the row inserted. Within an OLE DB Command in SSIS, I can call the stored procedure passing column values as the parameters and I usually use output parameters on the stored procedure to handle "id" output; but I am unsure how this can be handled with return values when the procedure uses a select to return the id value. Here is an example of what I have used before which works but I need to pick up the value returned from the select:

exec dbo.uspInsertContactAddress
@Address = ?,
@ContactID = ?,
@DeliveryMethodId = ?,
@ID = ? output,
@Version = ? output

Answer 1

The way I found I could do this which was actually quite simple:

exec ? = dbo.StoredProc @param = ?, @param2 = ?

and then a @RETURN_VALUE will appear on the Available Destination Columns

Answer 2

Don't use the variable names in the SqlCommand property, just the question marks and the "OUT" or "OUTPUT" label for the output parameters.

The trick for grabbing the output parameter value is to put a derived column transformation in the pipeline ahead of the OLE DB Command to introduce a column (mapped to an SSIS variable) to capture the procedure result.

See OLEDB Command Transformation And Identity Columns for a good overview with screen caps of how to do this. Also see Trash Destination Adapter for the Trash Destination used in the first link. It's a handy tool to have available for debugging things like this.