How To Deal With Slowly Changing Data In SSIS

In a data warehouse it is important to keep historical data as well as the new data.. In SSIS there is a way to get slowly changing data using Slowly changing dimension under data transformation task..It is a way of inserting data from source to destination with the Type 1 and Type 2 changes.

First you should create the source using a proper data source .Then drag and drop the slowly changing dimension task and give the specification as shown below

Connection Manager – Make a connection to the source and give that connection, it can be a database or an excel sheet.

Table or view – Specify the source table

Input Columns – Columns from the source

Dimension Columns – map the source columns with the destination columns

Key Type – Specify the primary key/Composite keys

And click next

Select a change type for slowly changing dimension columns specify the dimension columns sand the change type (in a data ware house all attributes should be changing attributes other than attributes like ( last updated date/date created) if you put any attribute as fixed attribute it will not updates the data when it comes for type 1 changes so I prefer not to use fixed attribute type if an attribute is not changing attribute then do not mention it in the changing attribute columns) click next

Inferred Dimension Members – enable inferred member support only if you think fact tables may reference dimensions members that are not yet loaded

(you should keep it as disable if you are not sure about it).. Click next

In the fixed and changing attribute option select change all the matching records, include out dated records, when changes are detected in changing attributes and finish slowly changing dimension wizard

It will automatically create the dimension with appropriate type 1 and type 2 fields where if an attribute is type1 it will updates the specific column if its type 2 it will insert as a new record.

You can rename the task accordingly using properties.