Slowly Changing Dimension Type 1 Changes using SSIS SCD Component

The Slowly Changing Dimension Component included with SSIS is one of the methods you can use to manage slowly changing dimensions, and its pretty easy to use.  The problem with the SCD component is that it performs pretty badly, especially as the number of rows in your table grows.  I would say that any Dimension with over 50,000 records in it would be too big for this component.  For small dimensions it will work just fine.

Once you have configured your source component and placed the SCD component in you design pane and hooked the two components up, double click on the SCD component to open the editor.

The first step is to connect to your destination table.  Then you need to select the column or columns in the destination table that match the key or keys from your source table.  In this instance we are choosing the UserAK column which is the Primary Key in our source Table and the Alternate Key in the destination table.  Once the Key columns are selected click NEXT.

On this screen you tell the wizard which of you non key columns are you going to update.  You do this by selecting one of the options from the dropdown menu under the Change Type column next to each Dimension Column.  There are three options:

1. Fixed Attributes, which means that the data in these columns won’t ever change, even if a change comes through from the source. 
2. Changing Attributes which corresponds to a Type 1 change.
3. Historical Attributes which corresponds to a Type 2 change.

Since that we are only worried about Type 1 changes we are going to select the Changing Attribute option.  Once all the columns are configured as you would like them click next.

Here we will configure how to handle the Fixed and Changing attributes. The first options determines how we want to handle updates that come for fixed attributes, either fail the transform or not. The next options allows you to update columns in historical records as well as the current record that are changing attributes. Once done here click next.

On the next screen you configure how to handle inferred members, for our purposes we will just leave this option disabled. Click Next and Finish.  The wizard will now add both an OLEDB Destination component and OLEDB Command component to handle the inserts and the updates and configure them for you.

Please don’t let the relative ease of setting up the SCD component drive your decision to use it.  If you expect your dimension to get fairly large there are other design patterns that you can use that will produce much better results.