SSIS-Implement SCD type2 without using CDC transformation

1 checksum – select all of type 2 columns, and add one output column as NEW CHECKSUM

2 lookup to see whether it is new record ,

-          Lookup column : only select PK, checksum from target table

-          Mapping : only mapping PK , but should check the checksum in the lookup columns, and change the output alias to OLD_CHECKSUM

3 lookup no match output path : as the new record to add the target table

4 lookup match output path : as the existing record

5 lookup checksum

-          Lookup column : only select checksum from target table

-          Mapping : mapping checksum in the lookup columns with available columns new checksum, and change the output alias to OLD_CHECKSUM

6 Conditional Split to output the changed rows : create case 1 as NEWCHECK != OLD_CHECKSUM

7 update the targe table to change active flag to N

-          Create an External Columns as Param_0 and mapping the PK

-          UPDATE target table SET ACTIVE_FLAG='N'  WHERE TERRITORY_ID = ?

8 insert new type 2 row