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 2 -using one Lookup and one Execute Sql Task (Alternative of SCD)
Method 2 : using one Lookup and one Execute Sql Task (Alternative of SCD)
DFT Contains an OleDB Source, which is tmpdimDest table, and one lookup which points to ‘dimDest’ table and compare 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 configured in OleDB destination.
As Execute Sql task, performs batch operation, so it is much faster than OleDb Command. So here we are using EST for updating the rows in the destination table ‘dimDest’ as below