Part 1: Incremental Load

Incremental Load (Delta Load / Upsert)

In this design pattern, SSIS is used to identify which rows are new and which rows need to be updated are sent to a staging table (Based on ‘Create Date’ and ‘Last Modified Date’ at source), which is later joined to the destination table to do the update. The advantage of this approach is that only the rows to be updated are written to disk twice.

Methods of Incremental Load

1. Write the matching keys to a new table, then DELETE from current table, and re-insert: TSQL approach not in fashion
2. Write to a Staging table, then do a TSQL MERGE: Can only be taken care when source and destination are on same server.
3. Using Lookup Component: The Lookup component, with Full Cache, is pretty fast. The big problem with a lookup component with full cache is that all data sits in RAM, so you need enough RAM for it all to sit there!
4. Using Hash Function with lookup component: In this approach, we maintain hash value in destination table; create hash value during transformation on source rows.
5. Using MERGE JOIN: The main drawback of this approach is, here we need to use fully blocking component (a-synchronization)  sort as well as semi –blocking component (a-synchronization) Merge Join to complete their operation. In this approach, one side we use source table as a source and another side destination table as a source after sort join both these 2 dataset with left join via Merge Join component, later then split the output as matched and unmatched rows, Unmatched rows are directly uploaded at destination, whereas matched rows again compared with hash value from source and destination.
6. Using Lookup Component with customize SCD Type 2: This approach is basically used for Historical Dimension or Fact (SCD Type2)

Continue with my Next Posts:

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
Part 6: Incremental Load - Using Lookup Component with Customized SCD (Slowly Changing Dimension) Type 2