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

Sile

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

Sile

I needed to use a parameter name of 0 in the parameter mappings section (sql313.com/index.php/39-main-blogs/maincat-dba/…) – Aegyptus

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.

Counterclockwise

Thanks for the answer, though maybe I didn't explain in my question exactly what I need to do. The above code I have used works and I can grab values if the procedure has an output parameter. The problem is that the procedures being used for this database just calls a select to get a value. – Sile

I'm sorry, I'm not sure I'm getting what the problem is. Is it that you're not passing the result of the select out via an output parameter and you instead need a way to grab the result set of the proc? – Counterclockwise

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.

Walkin

Answer 4

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.

Milo