For any ETL developer, updating dimensional data is the heart of what you do. Using out of the box SSIS components, however, is an unattractive proposition. You either had to use the built in SCD wizard, or use the "roll your own" approach. As any veteran BI developer knows, the SCD wizard isn’t the best in the world, primarily due to it’s reliance on the OLEDB Command task. "Roll your own", in other words handling all the logic yourself, works, but is time consuming to develop and often confusing to maintain.
A far, far better solution for handling Kimball SCD is to use the Task Factory SSIS Dimension Merge Slowly Changing Dimension transform. While it’s name is rather long winded, it’s definitely worth the breath.
In this video we’ll take a look at the Dimension Merge Slowly Changing Dimension in action.