INF: DTS Row Level Restartability After an Unexpected Failure (242543)

SUMMARY

If the Data Transformation Services (DTS) Pump task task fails half way through (and the InsertCommitSize is not 0), you might want to correct the problem on the source and restart the pump. However, you do not want to attempt to pump the rows that have already succeeded.

MORE INFORMATION

There are two solutions to this problem:

The first and fastest method is to use a SELECT statement as the source of the pump, which joins the source table with the destination table and only returns rows that are not in the destination table. For example:
```
select O.* from orders O where O.ID not in (select ID from DWOrders)
						
```
While SELECT NOT IN joins like this are expensive, they are not nearly as expensive as attempting a transformation for every row. This requires some sort of key for comparison between both sides. However, you can build a surrogate key.
If you are performing a Script transform on each row anyway, then another technique you can use is to call an UPDATE Lookup with each row to update the source and set a flag indicating that it succeeded. The Pump's SELECT statement filters out all the rows where the flag is set.

This method takes advantage of the fact that Lookups can be UPDATE statements as well as SELECT statements. Perform the Lookup on a connection other than the source connection and set the Lookup cache to 0.