SSIS : Method 1- Using two lookups instead of SCD (Slowly Changing Dimension )

  

Method 1: Using two lookups instead of SCD (Slowly Changing Dimension )

This method is part of SCD(Slowly Changing Dimension) and Oledb Command

Here the OLEDdb Source is ‘tmpdimDest’ table.

·         First lookup points to ‘dimDest’ table and compares the business key in ‘dimDest’ table with matching column in the ‘tmpdimDest’.

·         For ‘Lookup no match output’- means, the rows in ‘tmpdimDest’(source) table for which business key doesn’t match with ‘dimDest’ table, that means those rows are new in ‘tmpdimDest’ table , so insert operation is performed in ‘dimDest’, which I have confirgured in OleDB destination.

·         For ‘Lookup Match Output’ – means, the rows for which Business key matches in both table ‘dimDest’ and ‘tmpdimDest’ .But it’s possible that some modification or updation has been made in the information of some Business keys. So those rows should be updated or modified in ‘dimDest table.

·         Second look up again points to ‘dimDest’ table and compares all the columns in ‘dimDest’ with all the columns in ‘tmpdimDest’ table as shown below

For ‘Lookup No Match output’ the rows are updated by mapping the business key column in the OLeDB command as shown below

Update dimDest

dd.coulmn1=tdd.Column1

dd.column2=tdd.Column2

…

From dimDest dd , tmpdimDest tdd

Where dd.Busineskey=tdd.BusinessKey

This method is one of the alternatives of SCD.

 For all other alternative methods of SCD

For more information on SCD
For implementation of SCD type1 step by step
For implementation of SCd type2 step by step

Please provide your feedback for the post, if you find this post useful. Also Post your query or scenario, i will be happy to help.