Custom SSIS Slowly Changing Dimension Type 2

Unfortunately when you use the SCD wizard to create a type 2 slowly changing dimension you have to choose between using a current indicator or a valid date range technique. You can not have both, at least through the wizard. To add both you will have to edit the package after the wizard is complete.

Complete the Slowly Changing Dimension wizard. I prefer choosing the Single Column / Current Flag option for Historic Attribute Changes during the wizard.

Modify the components in the SDC update branch

1. Add a new derived column with an expression like (DT_DBTIMESTAMP) GETDATE()
2. Modify the OLEDB Command Update statement, i.e.:UPDATE [dbo].[tbl_Cost_Center_Dim] SET [Current_Flag] = ? ,Row_End_Date = ?WHERE [Cost_Center_Code] = ? AND [Current_Flag] = '1'
3. Modify the OLEDB Command, Column Mapping tab, to reflect the re-alignment the new parameter(s) added.

Modify the components in the SDC Insert path. These are the components after the Union All component.

1. Add new derived columns for begin and end effective dates. The end effective date should be a conformed standard future date such as 6/6/2079.
2. Add the new columns to the mapping of the OLEDB Insert Destination component.

In addition to using this process for Row Effective dates it is also useful for using Row Created and Row Updated dates.