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):
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.
There are several ways this can be done, but before I get into that I want to point out a potential performance issue. If you truly need to process these rows and acknowledge them one at a time, this becomes more of a RBAR (Row-By-Agonizing-Row) solution rather than the preferred set-based methodology. You would be repeating the following 2 steps for each row.
But do you really have to do that? What is the likelihood that you will lose contact with database #2 during the process? Perhaps you could insert a batch and then acknowledge the batch. If errors occur, delay handling them until after both steps have completed. There are various ways to accomplish this depending on your specific requirements. You mentioned inserts and updates, and you also mentioned an OLE DB insert command. I believe the updates will be the trickier part for handling errors. What could cause an error with those? Do (or can) you insert any timestamp or other data that indicates which records have been inserted (or updated) in database #2?
More information will help us give you a good recommendation. Otherwise, the danger is that a bad recommendation is sometimes worse than none at all.
answered Nov 10 '09 at 08:16 AM