8. Implementing incremental loads and SCD in SSIS

Pattern 1: Lookup-based upsert

OLE DB Source (changed rows since watermark)
  → Lookup on target by business key
      ├─ No Match → OLE DB Destination (fast load INSERT)
      └─ Match    → Conditional Split (columns changed?) → OLE DB Destination to a staging "updates" table
Execute SQL Task after the data flow: UPDATE target FROM staging updates (set-based)

Pattern 2: Load staging, then MERGE — Data Flow loads staging; an Execute SQL Task runs a T-SQL MERGE or SCD2 statements. Usually the fastest and simplest for large volumes.

SCD wizard — quick to configure (Fixed = Type 0, Changing = Type 1, Historical = Type 2, inferred members) but uses OLE DB Command per row → slow on big dimensions; replace with set-based SQL in production.

Watermark — Execute SQL Task reads LastLoaded into a variable → source query uses it → final Execute SQL updates it on success. Or use CDC Control Task + CDC Source + CDC Splitter (2012+) with SQL Server CDC.