hi, Consider the scenario:
I have two databases. From a table in first database I need to read some rows and insert\update the same to a table in second database and after inserting\updating the same I wana acknowledge the source database by setting a column let’s say ‘isProcessed’ to ‘Y’. I wana do this on row level basis. So, that if the package fails somehow in between we need not restart from the beginning.
I tried implementing the same using the following steps(explaining it giving an example):
- adding a data flow task and in that adding a OLE DB data source
- adding a OLE DB command to execute insert statement for each row in the data source above
- adding a derived column to fetch the acknowledgment returned by the stored proc executed above (this is added after step 1 and b4 step 2)
- based on the result set achieved above updating the source database
as you can see that the step 4 is performed only after step 3 is executed for all the rows obtained from source in step 1.
Please provide a solution.