SSIS Slowly Changing Dimension Type 1

In this article, we will explain about SSIS Slowly Changing Dimension Type 1 (called Changing attribute).

SSIS Slowly Changing Dimension Type 1: If you want to update the columns data, mark them as Changing attributes. If your Dimension table members (Columns) marked as Changing attributes, it replaces the existing records with new records. This type of change is useful. For example, if we want to update the wrongly typed data, mark this column as a Changing attribute. Before we step into the example, let us see the data inside our Dimension table.

NOTE: SSIS Slowly Changing Dimension Type 1 will update the old records with new values, but it doesn’t maintain the historical data in SSIS.

The following screenshot will show the data inside our [Employees] table. You can get the same data by selecting TOP 10 records from [DimEmployee] table in [AdventureWorksDW2014] database.

SCD Source Table

The below image shows the data present in the [EmployeeSource] table, and we are going to update or insert these two records into the [Employees] table.

SCD Source Table 2

If you observe the above image, the Department Name of the EmployeId (253022876) is different from original value (it has changed from ‘Marketing’ to ‘Sales’ and We have a new record with EmployeId 253022870. Our task is to insert the new record and update the Department Name of the second record.

SSIS Slowly Changing Dimension Type 1 example

STEP 1: Open BIDS and Drag and drop the data flow task from the toolbox to the control flow. Next, name it as SSIS Slowly Changing Dimension Type 1.

SSIS Slowly Changing Dimension Type 1 - 3

Double click on it will open the data flow tab.

STEP 2: Drag and drop OLE DB Source, Slowly Changing Dimension from SSIS toolbox to data flow region

SSIS Slowly Changing Dimension Type 1 - 4

STEP 3: Double click on OLE DB Source. Here we are using already created OLE DB Connection Manager and EmployeSource table present as the Source table.

SCD OLED DB Source Editor

STEP 4: Click on the columns tab to verify the columns. In this tab, we can uncheck the unwanted columns also.

SSIS Slowly Changing Dimension Type 1 - 6

To use the data inside the OLE DB Source, Drag and drop the blue arrow from OLE DB Source to SCD Transformation.

STEP 5: Double-click the Slowly Changing Dimension transformation to open the Slowly Changing Dimension Wizard.

SSIS Slowly Changing Dimension Type 1 - 7

STEP 6: Select a Dimension Table and Keys: This page is used to configure the Dimensional table information.

Connection Manager: This option provides a connection to the dimension table. Here we are using already created OLE DB Connection Manager that connects with the database present in our Server.

SCD Select Dimension Table and Keys

Table or View: Useful to select the Data Warehouse Dimension table or View on which you want to implement slowly changing operation. Here we are selecting the Employees table.

SSIS Slowly Changing Dimension Type 1 - 9
  • Input Columns: Columns coming from the OLE DB Source.
  • Dimension Columns: Columns available in Employees Dimension table.
  • Key Type: To perform Slowly Changing Dimension 1 or SSIS SCD 1, we need at least one Business key. And we know the Employee Alternative Key is the key column. Please change the Key type from Not a Key Column to Business key
SSIS Slowly Changing Dimension Type 1 - 10

STEP 7 – Slowly Changing Dimension Columns: This is the main page in this whole wizard.

  • Dimension Columns: Columns available in the Dimension table will be available in this section. Select the columns that you want to select for Change Type. Here, we are choosing the Birth date, and Email Address columns as Fixed attributes and Department Name as changing attribute
  • Change Type: Select the change type. Here, we are keeping the default Fixed attribute as change type for Birth date, Email Address columns, and Department Name as changing attribute.
SSIS Slowly Changing Dimension Type 1 - 11

STEP 8 – Fixed and Changing Attribute Options: This page has two options, and we already explained the first option in Slowly Changing Dimension Type 0 article. We will explain the second option in the next article.

SSIS Slowly Changing Dimension Type 1 - 12

STEP 9 – Inferred Dimension Members: We will discuss this option in the Slowly Changing Dimension Inferred Dimension article. For now, we are de-selecting this option and click Next button

SSIS Slowly Changing Dimension Type 1 - 13

STEP 10 – Finish the Slowly Changing Dimension Wizard: Click finish button to finish configuring the SSIS Slowly Changing Dimension Type 1

SSIS Slowly Changing Dimension Type 1 - 14

Once you click on the finish button, our Data Flow automatically changed. If you observe the below screenshot, it added the OLE DB Destination to insert new records into the Dimension table and OLE DB Command Transformation to update the existing Old records with new data

SSIS Slowly Changing Dimension Type 1 - 15

(Optional Step): Let us double-click on the OLE DB Destination to check the automatically created destination (table name and connection manager setting and mappings).

SSIS Slowly Changing Dimension Type 1 - 16

(Optional Step): Let us double-click on the OLE DB Command Transformation to check the Update statement that created automatically.

SSIS Slowly Changing Dimension Type 1 - 17

Click OK to finish our SSIS Slowly Changing Dimension Type 1 package design. Let us run the package

SSIS Slowly Changing Dimension Type 1 - 18

From the above screenshot, you can see that two rows are coming from the OLE DB Source. One row inserted, and the other row is updated.

Let’s open the SQL Server Management Studio and check the results. If you observe the below records, our package added New record of Employee Alternative key 253022870. And we have also updated the department name of Employee Alternative key 253022876.

SCD Type 1 Output

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.

Comments are closed.