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 Type1 Step by Step
Type 1 , step by step
Type 1 (changing attribute):
When the changes in any
attribute or column overwrites the existing records.
considering Type1, I assumed that the changed made in FirstName, Middlename and
last name of an employee will be overwritten. For making such scenario, I have
created two tables; one is tmpPerson, which is created as follows by using
createtable tmpPerson(EmpId int, Title varchar(10)Null,FirstName varchar(20), MiddleName varchar(20),LastName varchar(20),
HumanResources.Employee e innerjoin Person.Person per on e.BusinessEntityID=per.BusinessEntityID
also created one table tmpPersonStage similar to tmpperson, which stores the records
which are changed by changing any of the column value. For Type 1 I have made
some changes in FirstName, MiddleName and LastName, as below:
createtable tmpPersonstage(EmpId int, Title varchar(10)Null,FirstName varchar(20), MiddleName varchar(20),LastName varchar(20),
tmpPersonstage set FirstName ='Robert'where EmpId=3
tmpPersonstage set LastName ='Goldb'where EmpId=6
tmpPersonstage set FirstName ='Josse'where EmpId=8
tmpPersonstage set MiddleName ='L'where EmpId=49
tmpPersonstage set FirstName ='Kendal', LastName='Keill'where EmpId=58
preparing the initial steps for the scenario, let’s move to SSIS package to
apply SCD transformation. The below steps are the implementation of SCD Type 1
Drag and drop one Data Flow Task in the
Control flow tabs.
this DFT as ‘scd type 1’
Data Flow tab, Drag and drop OleDB Source and Slowly Changing Dimension
transformation from Data flow transformations.
Configure OleDB source, here the source will be the
table which has changed or new records, which will be updated or inserted in
the main table. In our scenario the source table is ‘tmpPersonStage’, which
keeps some updated and new records which will be updated or inserted into the
mail table ‘tmpPerson’
Configure the SCD transformation; double click on SCD
transformation, one wizard will open as below:
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 row
in ‘tmpPerson’ will be updated with the changes
from ‘tmpPersonStage’ input table
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 changing
attribute we will select some columns which will be overwritten if found
Next and select the below option,
next and unselect ‘Enable inferred member support’, as we don’t require it for
which displays New Records, Updated, other Outputs, which means there will be
three arrows from SCD, one will take the new records, another will take the
records which needs to be updated, and other outputs, which can be used for
and Finish the wizard. As we finish the wizard we see some transformation like ‘OleDb
command’ and ‘Insert Destination’ are automatically created. Oledb command is
for upfated records and insert destination for new records.
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.
when clicked on Component Properties tab then the update command is also
configure, which we can see on string value editor.
update command updates records on the basis of EmpId
column mapping tab the parameter are mapped with the columns mentioned in
query. These Parameters contain the changing attribute columns (firstname,
middlename ,lastname and one business key’empid’) from SCD
Destination will also be configured automatically, to insert the new records
coming from SCD to tmpPerson table.
Execute the DFT
execution, we can see that , we updated 5 records in the tmppersonstage table,
which are updated in tmpperson table also.As there was no new records , so no
row is inserted into tmpperson.