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
Sunday, April 7, 2013
SSIS : Method 1- Using two lookups instead of SCD (Slowly Changing Dimension )
Using two lookups instead of SCD (Slowly Changing Dimension )
·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