Wednesday, October 10, 2007

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.


Todd McDermid said...

I know this is an old post Elliott, but if you're still frustrated by that behaviour, you might want to try the Kimball Method SCD component on CodePlex. It addresses this, and a lot of other issues people usually have with the Wizard.

Elliott said...

Thanks Todd. I'll take a look at this component and share it with my team.