SSIS return value of Stored Procedure within an OLE DB Command

sql-server stored-procedures ssis oledbcommand
bobwah picture bobwah · Jan 28, 2009 · Viewed 51.5k times · Source

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

bobwah picture bobwah · Feb 4, 2009

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

Related questions

How do you call a Stored Procedure in SSIS?

I am trying to create an SSIS package that queries data from a table, and calls a stored procedure in another database with each row. In my old DTS package, I was doing this: EXEC myStoredProcedure ?, ?, ? ...and then I mapped …

Read More
Execute SSIS package from stored procedure as proxy user without xp_cmdshell

I am trying to run an SSIS package through a stored procedure, but I am getting an Access is denied error when I try to import a CSV. I put the package inside a job and ran it and it …

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More