Search code examples
ssisdataflow

SSIS - DataFlow - Set a value for each row from a procedure stocked result

For a SSIS, i have to transfer data from a server to another but on the final destination, i have a column like ID. For determinate the new ID of each row, i have to execute a stored procedure on the final server.

Is-it possible to do that in DataFlow and avoid the Foreach Loop in ControlFlow ?

Regards, Guillaume.

Solution

  • You can do this task using OLEDB command . If your SP has a return statement than try to capture the return value and map it with your input column 

    exec ? = dbo.ReturnID

    This statement will create a @RETURN_VALUE which you need to map with the input ID column .Remember to create an Input ID column in the source itself as NULL or any value so that you can map this column in OLEDB component with the @RETURN_VALUE 

    Update 1 :

    Try this :-

    EXEC dbo.my_sp 'p1', 'p2',@variable_name=? output

    @variable_name is the output variable name used in your stored procedure