Home > SSIS > Detecting changes in the fixed attribute using SCD Task – SSIS

Detecting changes in the fixed attribute using SCD Task – SSIS


Fixed attribute option in SCD task is used to detect the changes in the fixed value column. Fixed attributes in the source table contains data that should never change. For instance, Date of birth, SSN, Bank Account no., and etc. But, if the original data contains typo then we have modify the data in other ways. SCD task can be configured to fail the task if it detects the change in the fixed attribute or we can redirect the incorrect row to another destination.

We will see two demos for both scenarios in this blog post.

Implementation

Main table: tblState

pic1Staging table: tblState_tmp

pic2

1. In this demo, I will show how to configure the SCD task to fail if the fixed attribute has  change in data coming from the staging table.

Step 1: Create a new package and Add at Data Flow task

Step 2: Go to Data flow designer and add a OLE DB Data source and connect to tblState_tmp stating table.

Step 3: Add a Slowly Changing Dimension task and connect data source task to SCD task.

pic3Edit SCD task and skip the first page.

pic4Select OLE DB source and select the Main table and business key column.

Click next.

pic5Select the Fixed attribute column.

Click next.

pic6Mark the Check box to fail the transformation if it fined the changes in the fixed attribute.

Click next.

pic7Uncheck “Enable inferred member support” option.

Click next.

pic8Click Finish.

pic1Step 4: Execute the package. SCD task will fail because. StateName in the staging table ‘Texass’ and the main table has ‘Texas’. So, SCD cannot change the destination table.

pic22. In this demo, I will show you have to redirect the incorrect row without failing the package.

Use the previous demo to implement Step 1, Step 2 and In step 3 and page 4 remove the check mark from this dialog box.

pic1In step 4, Add a Union All task and connect the SCD pipe line to redirect the Fixed attribute row.

pic2Click OK and execute the package.

pic1

Advertisements
  1. Hari
    November 26, 2013 at 12:12 pm

    Nice Article… 🙂

  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: