I have SSIS package that I am trying to get data from a express server on one box and move it to a principal server and if the record is inserted in the principal server it must be deleted from the express server.
This operation is being done in a loop because data is being loaded from multiple express servers. So I have used a foreach loop and inside I placed a data flow control.
Inside the data flow I have a ole db source and an ole db command. I use the source to do a select on the express server to get the data and pass it to the ole db command which has a store procedure that inserts the records.
The sql command looks like
EXECUTE [Store Procedure] ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?output.
I want to store that out put in a variable and at the end delete all the records from the source data express database as not to re import them at a later date. Is that possible or am i going about it the wrong way. Any help would be appreciated. Thanks in advance.