Part 6: Incremental Load - Using Lookup Component with Customized SCD (Slowly Changing Dimension) Type 2

Continue with my Previous Posts:

Part 1: Incremental Load

Part 2: Incremental Load - Using Lookup Component and Hash Function

Part 3: Incremental Load - Using Lookup Component

Part 4: Incremental Load - Using MERGE JOIN
Part 5: Incremental Load - Using TSQL Merge Function

Package: MSSQLTIPS_Hash_SCD2.dtsx

This approach is very much similar as Incremental Load using Hash, The basic difference is, it is customize with SCD Type 2

A. Change Lookup source as below ((LKP) Match Rows With Destination)

SELECT SK_Customers,CustomerAlternateKey,DWH_Hash FROM MyDestination Where CurrentFlag = 1;

B. Add Derived Column Component as below

C. Change update statement as below ((SQL) Update Destination Table)

UPDATE dest

SET        SCD_EndDate = GetDate()

                ,CurrentFlag = 0

FROM [dbo].[MyDestination]              dest

JOIN [dbo].[UPD_MyDestination] upd ON dest.SK_Customers = upd.SK_Customers;

D. Add Data Flow Task (DFT) Load Updates into Destination Table to insert modified rows with new start date and current flag 1.