Home > SSIS > Implementing SCD Type 1 – SSIS

Implementing SCD Type 1 – SSIS


Slowly Changing Dimension (SCD) Type 1 methodology overwrite the existing data with new data. So, we cannot track the changes occurred in the past. The best situation this type can be applied to a table is to alter the spelling of the name column or some thing that does not affect the meaning of the data.

I am using Slowly Changing Dimension task to implement SCD Type 1 in this blog post.

Implementation

I have a table with few rows as shown in the image

pic1I have found row 8 has a misspelled state name Texass instead Texas. So, I am going to change this value using SSIS SCD component. But, I need to use another temporary table to hold the new value as an input table for SCD task.

Input table State_temp

pic2

Step 1: Create a package and add a data flow task

pic3Step 2: Add a ADO.net source Task and connect to input table

pic4Step 3: Add Slowly Changing Dimension task and connect ADO source task with it.

pic5Configure SCD Comonent

Edit SCD task and skip the welcome wizard. Click next.

SCD task uses OLE DB connection only to connect to source table. So, We have to create new OLE DB connection and select the main table and set the business key. Business key is unique key for that table and it is used for unique row identification. This is mandatory for SCD task. Configure the task as given in the image.

pic6Click next

Now, Select the name column and choose the SCD type. You have 3 options

  1. Fixed attribute – It means that the column value will never change.
  2. Changing attribute – SCD Type 1
  3. Historical attribute – SCD Type 2

pic7click next.pic8Click next.

pic9Uncheck the “Enable inferred member support” and click next.

pic10Click finish

Step 4: Execute package.

pic111 row has been updated for change attribute. Now, Explore the source table to see the changed data.

pic12This is a simple example to implement SCD type1 using SCD task in SSIS.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: