0

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.

Guillaume
  • 844
  • 7
  • 25

2 Answers2

1

No, I don't think it is. I suggest following the DataFlow task with an Execute SQL task that will execute your stored procedure.

NigelK
  • 8,255
  • 2
  • 30
  • 28
  • As per OP ,the SP is going to create a new ID for each row .If you get this value in `Execute SQL Task` then how will you map in `Data Flow Task` .Chunks of rows are processed in `DFT` – praveen Jan 24 '13 at 09:24
  • Yes, the problem is for each row :s – Guillaume Jan 24 '13 at 09:34
1

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

praveen
  • 12,083
  • 1
  • 41
  • 49
  • Ok I had add an empty column in my OleDb source and linked to an OleDb Command. But i don't understrand how to store the result of SP in the empty column. The Column Mappings tab is empty. – Guillaume Jan 24 '13 at 09:32
  • When you write this statment `exec ? = dbo.ReturnID` in `oledb command` it will automatically create `@return_value` in `Available Destination column` in `Columns mapping tab` – praveen Jan 24 '13 at 09:36
  • OK but the SP has a last parameter in OUTPUT, so in the SqlCommand i set : DECLARE @ TEST INT; EXEC dbo.my_sp 'p1', 'p2', @ TEST And nothing appears in the tab. I tried your syntax but i got errors. – Guillaume Jan 24 '13 at 09:43
  • Ok my mistake. I finally understand your answer. It's great ! Thank you – Guillaume Jan 24 '13 at 09:54