Feedback Requested: SCD Technique

Hi folks, I have implemented this technique to simplify SCD loads and also to maintain consistent units of work during update/insert of a single row. Wanted to get your feedback on this technique: performance, transaction issues, etc.

I send all rows to an OLE DB Command that performs both update and insert for a single row in a single command:




Code Snippet
UPDATE PROPERTY SET ORD_TERM_DT = ? WHERE ACCOUNT_NBR = ? AND ORD_TERM_DT = '9999-12-31 23:59:59';

INSERT INTO PROPERTY (
ACCOUNT_NBR
, APPRAISAL_COMPANY_CD
, .....
, ORD_TERM_DT
) VALUES (?, ...,?);



This way I can guarantee that if the termination (update) of an old row (say, row 10) succeeds, but insert of the new row 10 fails, that it will roll back. Otherwise, row 10 will get terminated without being replaced with a current record...


Performance: load of 7,734 changed records into a table of 6.8M existing records was roughly 8 seconds. The data flow task container TransactionOption = Required.