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:

---Start-TmpPerson table ---

CREATE TABLE tmpPerson (EmpId int, Title varchar(10) NULL,FirstName varchar(20),MiddleName varchar(20),LastName varchar(20),JobTitle varchar(100),BirthDate Date,MaritalStatus char(1),Genderchar(1),NationalIDNumber varchar(20),CurrentFlag int , StartDate date,EndDate date DEFAULT ('2050-12-31'))

INSERT INTO tmpPerson (EmpId, Title,FirstName, MiddleName,LastName, JobTitle,BirthDate,MaritalStatus,Gender,NationalIDNumber,CurrentFlag, StartDate)

SELECT e.BusinessEntityID, per.Title,
       per.FirstName,
       per.MiddleName,
       per.LastName,
       e.JobTitle,
       e.BirthDate,
       e.MaritalStatus,
       e.Gender,
       e.NationalIDNumber,
       e.CurrentFlag,
       e.ModifiedDate
FROM HumanResources.Employee e
INNER JOIN Person.Person per one.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:

---Start--tmpPersonStage table----

CREATE TABLE tmpPersonstage (EmpId int, Title varchar(10) NULL,FirstNamevarchar(20), MiddleName varchar(20),LastName varchar(20),JobTitle varchar(100),BirthDate Date,MaritalStatus char(1),Genderchar(1),NationalIDNumber varchar(20),CurrentFlag int , StartDate date,EndDate date DEFAULT ('2050-12-31'))

INSERT INTO tmpPersonstage (EmpId, Title,FirstName, MiddleName,LastName, JobTitle,BirthDate,MaritalStatus,Gender,NationalIDNumber,CurrentFlag, StartDate,EndDate)
SELECT EmpId,
       Title,
       FirstName,
       MiddleName,
       LastName,
       JobTitle,
       BirthDate,
       MaritalStatus,
       Gender,
       NationalIDNumber,
       CurrentFlag,
       StartDate,
       EndDate
FROM dbo.tmpperson
WHERE empid IN (3,
                6,
                8,
                58,
                49) ----changes for type 2

UPDATE tmpPersonstage
 SET JobTitle ='Design Engineer' WHERE EmpId=3
UPDATE tmpPersonstage
 SET JobTitle ='Production Technician - WC10' WHERE EmpId=6
UPDATE tmpPersonstage
 SET JobTitle ='Production Technician - WC50' WHERE EmpId=8
UPDATE tmpPersonstage
  SET JobTitle ='Research and Development Engineer' whereEmpId=49

UPDATE tmpPersonstage
SET JobTitle ='Engineering Manager',
      LastName='Keill' whereEmpId=58 ---End---tmpPersonstage table -------

In the above script, I have updated the JobTitle of some employees, for which initially the job titles in tmpPerson table were as below

 

After 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 2

Step 1:

Drag and drop one Data Flow Task in the Control flow tabs.

And name this DFT as ‘scd type 2’

Step2:

Go to Data Flow tab, Drag and drop OleDB Source and Slowly Changing Dimension transformation from Data flow transformations.

Step 3:

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 row  in ‘tmpPerson’ will be updated with the changes  from ‘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 "Use single column to show current expire records" 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.

I hope my effort of making you aware on 'SCD Type 2, step by step' helped you in your journey of SSIS.  If you feel any queries, please post them as comments below. Also, your feedback will be well appreciated as it helps us to improve better and better every time.