SSIS: Slowly Changing Dimensions (SCD Type 2)

In SQL Server Data Tools (SSDT) there is an item to be found in the toolbox that can create a whole workflow of a table historization with the help of a wizard.

Before we start, a small reminder of the different types of Slowly Changing Dimensions (SCD). I will only cover the first 3 here:

SCD Type 0: You retain the original values as they are by time of insertion

SCD Type 1: Values are overwritten. There is no history here. You simply always store the most recent value

SCD Type 2: Each time the value changes, you add an additional row in the table. So basically you have multiple entries for one natural key but multiple surrogate keys

Now, let’s move to SSDT and open a new SSIS project.

In the Data Flow tab we will need a OLE DB Source and this be connected to the item Slowly Changing Dimensions

Now click through the wizard and define a target server and table and what will be the business key

In the next step we need to define the Change Type. The wizard offers three types – I will map them to the SCD types I described above:
Fixed Attribute -> SCD Type 0
Changing Attribute -> SCD Type 1
Historical Attribute -> SCD Type 2
Since I want to historize the data, I will select Historical attribute

In the next part, we will therefor also need a start and end date:

 

I defined 2 variables in my package, whereas I will use the End variable here.

Unselect the Enable button on the Inferred Dimension members and go to the next page.

Click on finish and have a look at the new output

All we need to do now is add an OLE DB Destination item where the values will be inserted.

You will need to map the values in the OLE DB Destination item 

Alright, we’re almost there. Let’s update some values on the current table.

 

update stg.Artist

set Artist = '3 Deep'

where artistid = 9

update stg.Artist

set artist = '4 Low'

where artistid = 10

Now we need to run the SSIS package we just created.

Output:

As you can see, SourceArtistID (the natural key – AristID in the source table) 9 & 10 can now be found with 2 inserts each showing start and end dates.

In addition, we also have 3 new entries with a correct timestamp by using the start parameter.