SSIS return value of Stored Procedure within an OL

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

If the stored procedure returns a resultset, then you need to capture it:

DECLARE @results TABLE (
    [ID] INT NOT NULL
)

INSERT @results ([ID])
EXEC dbo.uspInsertContactAddress @Address = ?, @ContactID = ?, @DeliveryMethodId = ?, @ID = ? output, @Version = ? output

SELECT * FROM @results 

Note: I used a TABLE variable. You might need to use a temp table depending on your SQL Server version.

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.

Answer 3

I have always used the parameter mapping within the Execute SQL Task with a lot of success. The SQL Statement is "EXEC nameofstoredproc ?, ? OUTPUT", with the question marks specifying the location of the parameters and OUTPUT if the parameter is an output.

You specify the parameters in the mapping with the appropriate variable name, direction (input, output, ReturnValue) and data type. Since your stored proc is returning the data you want via a result set, then specify the direction for the variables to collect the ID and version as ReturnValue. It should work just fine for you.

Answer 4

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