I have created this blog to learn more and more about SSIS and Sql server.
Here you will find posts about Control Flow Task, Data flow transformation and lot more. I have tried to explain transformations and tasks through simple scenario and example.
I hope this would help you

Wednesday, June 12, 2013

SSIS: SCD Type 2, step by step

SCD
Type 2, step by step

Type 2 (historical attribute): when we need to maintain the history
of records, whenever some particular column value changes.

By
considering Type2, I assume that the employee gets promotion, Job Title changes.
In such case we need to maintain the history of the employee, that with which
designation or job Title he joined, and when his designation or Job Title changed.

For making such scenario, I have created two tables;
one is tmpPerson, which is created as follows by using Adventure Works database’s
tables:

from
HumanResources.Employee e innerjoin Person.Person per on e.BusinessEntityID=per.BusinessEntityID

----End
–TmpPerson table----

I have
also created one table tmpPersonStage similar to tmpperson, which stores the
records which are changed by changing any of the column value. For Type 2, I
have made some changes in Job Title, as below:

Configure OleDB source, here the source will be the
table which has changed or new records, and for which history will be maintained,
or inserted in the main table. In our scenario the source table is
‘tmpPersonStage’, which keeps some updated and new records for updated records
history will be maintained and new records will be inserted into the main table
‘tmpPerson’

Step 4:

Configure the SCD
transformation;
double click on SCD transformation, one wizard will open as below:

Click
on next and create either new connection or already created connection to
AdventureWorks2008, and while mapping the input columns to dimension column, at least one column need to be mentioned
as ‘Business Key’.

Business key is the column on the basis of
which the updation or insertion into ‘tmpPerson’ will be made. Here we have
mentioned EMPID as BusinessKey, so if EmpId of input table ‘tmpPersonStage’ is found in ‘tmpPerson’ table then the respective
rowin ‘tmpPerson’ will be updated with
the changesfrom ‘tmpPersonStage’ input table

Click
on next. In the below window, change type need to be mentioned on certain
column. Change Type can be ‘Fixed attribute’, ‘Changing attribute’ and
‘Historical Attribute’

As in this scenario is based on Historical
attribute we will select some columns for which history will be maintained if
found modified and new record will be inserted.

Go to
Next and select one of the below option,

As in ‘tmpPerson’,
we have both the option available one through ‘CurrentFlag’ column and another
through ‘Startdate’ and ‘EndDate’.

We can
opt for first option and use column ‘CurrentFlag’ as the indicator of the
current record. By this the updated record will be inserted with ‘CurrentFlag’
value as ‘1’ and the records which became old for the respective ‘EmpID’ the ‘CurrentFlag’
will be set to ‘0’.

And if
we opt for second option then we need to update the old record’s EndDate with ‘GetDate
()’ and need to insert the updated record with ‘StartDate’ as ‘GetDate ()’. For
new records, we need to use StartDate as ‘GetDate ()’ .

Through
this post, I am selecting first option, and I will be using ‘CurrentFlag’ Column to show the current record.

Go to
next and don't select ‘Enable inferred member support’, as we don’t require it for
this scenario.

Click next,
which displays New Records, other Outputs(Historical Attribute Output), which
means there will be two arrows from SCD, one will take the new records, another
will take the records for which history needs to be maintained.

Click next
and Finish the wizard. As we finish the wizard we see some transformation like
‘OleDb command’ and ‘Insert Destination’ are automatically created.

Oledb command will update the ‘CurrentFlag’
column of old records and will set it to ‘0’ for the ‘EmpID’ for which records
are updated. These all will be union all with all new records along with the
new updated records having ‘CurrentFlag’ 1 and will be inserted into the main
table ‘tmpPerson’

Step 5:

Step 5.a:

Double
click on Derived Column, and we see that one column with ‘Replace Currentflag’
has been derived from current flag and the value is set to ‘0’.

Step 5.b:

Double
click on Oledb command, and it will be configured automatically as below. The
connection will be automatically configured to table ‘tmpPerson’ of Adventure
works 2008, in which the records will be updated.

Also
when clicked on Component Properties tab then the update command is also
configure, which we can see on string value editor.

The
update command updates Current Flag to 0 on the basis of EmpId , and Current
Flag(value 1)

In
column mapping tab the parameter are mapped with the columns mentioned in
query. These Parameters contain the Replace Current Flag column from derived
transformation and EmpID from SCD

Step
5.c:

When
clicked on next Derived Column transformation, the same Current Flag column is
derived column of Replace Current flag, and set it to 1, for new and updated
records.

Step
5.d:

Insert
Destination will also be configured automatically, to insert the new records
coming from SCD to tmpPerson table.

Step 7:
Execute the DFT

After
execution, we can see that, we updated 5 records in the tmppersonstage table,
which are inserted into tmpperson table, along with the old records current
flag updated as 0. As there was no new records, so no row is inserted into
tmpperson.

Following up from my first comment, still think this is a great introduction. I found that Microsoft's SCD2 wizard lacked certain features (for example you can have either a current flag or an effective and expiry date but not both) instead I used SCD Merge Wizard as a plugin for SSIS 2008 and that has been incredibly useful. It's got a lot more functionality and is relatively easy to use once you get the hang of it.