What does the SCD transform in SSIS do, and should you use it?
Part three of a six-part series of blogs

Integration Services include a Slowly Changing Dimension transform, which is designed to make loading dimension data easier. This blog explains how it works, and discusses whether it achieves its objectives.

  1. Using the Slowly Changing Dimension Transform in SSIS
  2. Creating an SCD transform - type 0 (fixed attributes)
  3. Creating an SCD transform - type 1 (changing attributes) (this blog)
  4. Creating an SCD transform - type 2 (historical attributes)
  5. Inferred Members in Slowly Changing Dimensions
  6. The disadvantages of SCD transforms

Posted by Andy Brown on 05 July 2014

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Creating an SCD transform - type 1 (changing attributes)

We start in the same way as for the previous part of this blog.  That is:

  1. Create a new package.
  2. Create a new data flow task within it.
  3. Add an OLEDB source to take data from the vwPromotions view.
  4. Add a Slowly Changing Dimension transform.

This should leave you with this:

Basic data flow diagram

The data flow we'll start with. Time now to configure the SCD transform!


Configuring the SCD transform

Double-click on the SCD transform, and follow the steps through the wizard (passing straight through the first information message, if it's still appearing) as below.  Firstly, as before you are trying to load data into the DimPerson dimension table:

Choose DimPerson table

Choose to load data into the DimPerson table.

For the input columns, we're not interested in capturing when the first name or surname change (at least, not for this example), so remove these, and tell SSIS that the GradeName input column is the same as the Grade dimension column:

Choosing input columns

We'll dispense with the first name and surname columns, but we do want to capture changes in the grade.


So that SSIS can know which imported data row contains changes for which person, we need to specify which is the business key (usually this will be the unique key in the transactional database table);

Choose the business key

The business key is the column which allows SSIS to match up rows.

You can specify lots of columns in an SCD transform for which you want to capture changes, and they don't all have to have the same SCD type, but here things are very simple:

Changing attribute type

Make the Grade column have SCD type 1.


On the next stage of the wizard, you would normally want to capture changes (it's surely the whole point of creating the SCD transform in the first place):

Choose to change matching records

Tick this box to update the grade whenever a change is found in a person with matching PersonId.


Don't choose inferred member support:

Inferred member support enabling

We'll cover inferred members later in this blog.


Confirm your choices on the last stage of the wizard and finish.  SSIS will create for you a diagram similar to this:

SCD type 1 diagram

The SCD wizard generates two additional tasks.

The left-hand path will add any people found in the imported data who don't already exist in the dimension table; the right-hand path will update the grades for those people who do already exist in the dimension table.

Running the SCD transform

Time now to run your transform!  This is what our dimension table looks like to begin with:

Initial dimension table

The initial dimension table.

Running the package should give this:

The package when run

Four rows have been updated, according to this.

The resulting dimension table looks like this:

Final table of people

Rita has been promoted twice, but because no history is kept only the last one shows.

As a reminder, here was the view of changes which caused these updates:

Table of grade changes

The changes which we've passed on to the dimension table.

The drawback of type 1 SCD transforms

You'll already have spotted the obvious problem: we've lost any history of grades.  So if you want to know what Rita Smith earned in the first quarter of 2001, you can't.


Wouldn't it be nice if there was some way to capture the change history ... ?

This blog has 0 threads Add post