SSIS Slowly Changing Dimension Transformation
The Slowly Changing Dimension transformation is used to insert or update records in a table based on the business keys defined in the transform. It is the most powerful and complicated transform in a Data Flow Task and broadly used to change records in tables, especially in data warehouse dimension tables. Right now it only supports to connect to SQL Server and it has one input and up to six outputs without an error output. Let's see the diagram below to understand how it works.
- At run time, the Slowly Changing Dimension transformation will check if the key columns of its inputs match the key columns of the target table.
- If not matched, this means the row of the input is a new row and will go to the New Output.
- If matched, then check if at least one column is changed or not.
- If not changed, the row of the input will go to Unchanged Output.
- If changed, then check the change type of the column. There are 3 change types of each column and each type has different action and output. The types are summarized in a table below.
- If the inferred member support is enabled, the Inferred Member Updates Output will be generated and is used to change the inferred member record in the dimension table.
Change Type | Description | Output |
---|---|---|
Fixed attribute | Indicate the column of the target table cannot be changed. | Fixed Attribute Output |
Changing attribute | Indicate the column of the target table will be changed. | Changing Attribute Updates Output |
Historical attribute | Create a new row in the target table instead of changing the column. | Historical Attribute Inserts Output |
Before create a sample package, let's see what the source excel file C:\SSIS\Books_scd.xlsx looks like.
And we'll create a table to received the source data in database Test.
DELETE FROM lendout WHERE id = ? DELETE FROM student WHERE name = ?
Now follow the steps below to create the package.
- Open the project LearnSSIS2 which we created before and copy the package OLEDBCommand.dtsx and paste in the SSIS Packages folder in Solution Explorer. Then rename the package to SlowlyChangingDimension.dtsx.
- Open the new package and click Data Flow tab, then remove the Lookup and OLE DB Command transforms.
- Double-click the "Excel Connection Manager" to open its setting window and change the Excel file path to C:\SSIS\Books_scd.xlsx and click OK button.
- Double-click Excel Source to open the editor and click "Columns" tab to check the source data columns.
- Click OK. Then double-click the Data Conversion transform to open its editor and make the settings as shown below.
- Click OK. Drag & drop the Slowly Changing Dimension (SCD) from SSIS Toolbox to the edit surface and link the Data Conversion output to it.
- Double-click the SCD transform to open the Slowly Changing Dimension Wizard and click Next button to skip the welcome page.
- Make the setting below and then click Next.
- Set the "BoughtDate" to "Changing attribute", "Price" to "Historical attribute" and "Store" to "Fixed attribute".
- click Next. Uncheck the "Fail the transformation if changes are detected in a fixed attribute".
- Click Next. We'll set the Historical Attribute Options as shown below.
- Click Next. Uncheck "Enable inferred member support".
- Click Next. Then click finish button. Then the New Output, Historical Attribute Inserts Output and Changing Attribute Updates Output are created automatically.
- Drag & drop the 2 Row Counts to the edit surface and create & assign 2 variables to them.
- Link the Fixed Attribute Output and Unchanged Output of the SCD transform to the 2 Row Counts. Then enable the data viewers on all the outputs.
- Run the package.
- Run the package at the second time.
- Change the source data as follows.
- Run the package again.
Pay attention: The "Copy of Book Name" is chosen as the Business key.
We set the 3 columns in different types to see what will be happened later in the result.
This option is displayed because we set the "Price" to "Historical attribute" in step 9.
We'll not test the Inferred Member Updates Output in this example, so we just unchecked it here.
You will see all the 4 records go to the New Output. Because there is nothing in the table.
The 4 records are going to Unchanged Output.
The red squares indicates the changed data.
Book D goes to Fixed Attribute Output because the data in Store column was changed.
Book A, C go to Historical Attribute Output because the Price columns were changed.
Book B goes to Changing Attribute Updates Output because only the data in BoughtDate column was changed.
![]() |
---|
Fixed Attribute Output has the highest priority to have the output data, then Historical Attribute Output and at last Changing Attribute Updates Output. |
Let's check the data in Test database.
Apparently, the record 1 and 3 are expired records and the expired dates are set up.
I'm going to leave the reader to change the step 11 to "Use a single column to show current and expired records" by using the Flag field in the table.
The following SQL statement can be used to remove all the records in the table books_scd and restart running the package.
TRUNCATE TABLE dbo.books_scd