Task Factory Upsert Component - LastUpdate Update Method

Question

I recently ran across a situation when using one of Pragmatic Works' Task Factory components. I was using the Upsert task to handle Inserts and Updates, and chose the "LastUpdate Compare - Update based on 'last update' column value" method to discover changes in records.

When the package was released to QA, I was told that it was not capturing updates. My QA engineer was running the package, updating records in the destination, then re-running the package, expecting those updates to be overwritten. Instead, they were remaining in place.

After some time, I was able to get a copy of the script being used:

UPDATE dbo.Table
SET  OrderNum = 3  , DTModified = GETDATE() -3
WHERE PK1 = 800

Multiple query attempts failed to get the package to recognize this as a change. Based on the documentation from PragmaticWorks (here and here), any difference in the DtModified field should have triggered an update.

Last update compare will compare a date column from the source to a date column in the destination. If the dates do not match, the row is updated.

Answer 1

Unfortunately, it does not appear that the documentation is correct. After I changed the QA script to read

UPDATE dbo.Table
SET  OrderNum = 3  , DTModified = DTModified -3
WHERE PK1 = 800

the ETL package was able to recognize that the record had changed and overwrite the changed value with the data elements from the source system.

Based on this test, then, I assume that the Upsert component's actual functionality is more on the lines of:

Last update compare will compare a date column from the source to a date column in the destination. If the Source Column date is prior to the Destination Column date, the row is updated.

Since I was unable to find this documented anywhere else on the web, posting it here may bring the result in any similar searches for users who are having issue with the Task Factory Upsert Component not updating records modified in the source.

Though my scenario (updates in the destination by a QA engineer) is not something that will affect a Production box (hopefully), I can think of a couple other cases where this might affect something, such as if the source was rolled back to a previous snapshot.