The Type 2 Dimension/Flag Current mapping performs the following tasks:

The following figure shows a mapping that the Type 2 Dimension/Flag Current option in the Slowly Changing Dimensions Wizard creates:

The Type 2 Dimension/Flag Current mapping uses a Lookup and an Expression transformation to compare source data against existing target data. When you step through the Slowly Changing Dimensions Wizard, you enter the lookup conditions (source key columns) and source columns that you want the Integration Service to compare against the existing target.

For each source row without a matching primary key in the target, the Expression transformation marks the row new. For each source row with a matching primary key in the target, the Expression compares user-defined source and target columns. If those columns do not match, the Expression marks the row changed. The mapping then splits into three data flows.

The first data flow uses the Filter transformation, FIL_InsertNewRecord, to filter out existing rows. The Filter transformation passes only new rows to the UPD_ForceInserts Update Strategy transformation. UPD_ForceInserts inserts new rows to the target. A Sequence Generator creates a primary key for each new row. The Expression transformation, EXP_KeyProcessing_InsertNew, increases the increment between keys by 1,000 and creates a current flag of 1 for each new row.

In the second data flow, the FIL_InsertChangedRecord Filter transformation allows only changed rows to pass to the Update Strategy transformation, UPD_ChangedInserts. UPD_ChangedInserts inserts changed rows to the target. The Expression transformation, EXP_KeyProcessing_InsertChanged, increments the primary key by one and creates a current flag of 1 to indicate the updated row contains current dimension data.

In the third data flow, for each changed row written to the target, the Filter transformation, FIL_UpdateChangedRecord, passes the primary key of the previous version to the Update Strategy transformation, UPD_ChangedUpdate. UPD_ChangedUpdate updates dimensions in the target. The Expression transformation, EXP_UpdateChanged, sets the current flag to 0. This changes the status of the previous dimension from current to not-current.