SSIS Slowly Changing Dimension Transformation

<-- OLE DB Command

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.

[Slowly Changing Dimension Logic]

  1. 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.

  2. If not matched, this means the row of the input is a new row and will go to the New Output.

  3. If matched, then check if at least one column is changed or not.

  4. If not changed, the row of the input will go to Unchanged Output.

  5. 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.

    6. Change TypeDescriptionOutput
    Fixed attributeIndicate the column of the target table cannot be changed.Fixed Attribute Output
    Changing attributeIndicate the column of the target table will be changed.Changing Attribute Updates Output
    Historical attributeCreate a new row in the target table instead of changing the column. Historical Attribute Inserts Output

  6. 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.

Before create a sample package, let's see what the source excel file C:\SSIS\Books_scd.xlsx looks like.

[Source Excel File]

And we'll create a table to received the source data in database Test.

Select 
DELETE FROM lendout
WHERE id = ?

DELETE FROM student
WHERE name = ?

Now follow the steps below to create the package.

  1. 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.

  2. Open the new package and click Data Flow tab, then remove the Lookup and OLE DB Command transforms.

  3. 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.

    4. [Excel Connection Manager]


  4. Double-click Excel Source to open the editor and click "Columns" tab to check the source data columns.

    5. [Excel Source Editor]


  5. Click OK. Then double-click the Data Conversion transform to open its editor and make the settings as shown below.

    6. [Data Conversion Editor]


  6. Click OK. Drag & drop the Slowly Changing Dimension (SCD) from SSIS Toolbox to the edit surface and link the Data Conversion output to it.

  7. Double-click the SCD transform to open the Slowly Changing Dimension Wizard and click Next button to skip the welcome page.

  8. Make the setting below and then click Next.

    9. [Select a Dimension Table and Keys]

    Pay attention: The "Copy of Book Name" is chosen as the Business key.


  9. Set the "BoughtDate" to "Changing attribute", "Price" to "Historical attribute" and "Store" to "Fixed attribute".

    10. [Slowly Changing Dimension Columns]

    We set the 3 columns in different types to see what will be happened later in the result.


  10. click Next. Uncheck the "Fail the transformation if changes are detected in a fixed attribute".

    11. [Slowly Changing Dimension Columns]


  11. Click Next. We'll set the Historical Attribute Options as shown below.

    12. [Historical Attribute Options]

    This option is displayed because we set the "Price" to "Historical attribute" in step 9.


  12. Click Next. Uncheck "Enable inferred member support".

    13. [Inferred Dimension Members]

    We'll not test the Inferred Member Updates Output in this example, so we just unchecked it here.


  13. Click Next. Then click finish button. Then the New Output, Historical Attribute Inserts Output and Changing Attribute Updates Output are created automatically.

    14. [Slowly Changing Dimension Layout]


  14. Drag & drop the 2 Row Counts to the edit surface and create & assign 2 variables to them.

  15. 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.

    16. [Add Row Counts and Data Viewers]


  16. Run the package.

    17. You will see all the 4 records go to the New Output. Because there is nothing in the table.


  17. Run the package at the second time.

    18. [Unchanged Output]

    The 4 records are going to Unchanged Output.


  18. Change the source data as follows.

    19. [Source Data Updated]

    The red squares indicates the changed data.


  19. Run the package again.

    20. [Running Result]

    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.

    Note Note
    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.

    [Database Results]

    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.

    Select 
    TRUNCATE TABLE dbo.books_scd
<-- OLE DB Command