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
This method is much-much faster than.
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.
Execute SQl task can be used in Control flow , how you are moving Match rows from DFT to Control flow ?
ReplyDeleteNo i have not moved matching rows from DFT to control flow, i used everything inside DFT only. may be u r confused by seeing the first image of the post.
Deletethe EST is used to update all the records , which are modified and already existing in the destination table . In this post, i have first used DFT , to insert new records by comparing the business key in tepdimdest table and dimdest table.After the new records are inserted. I used EST to update all records in dimdest for which business key is also available in tmpdimdest table.
DeleteEVER THING IS CORRECT IN UR BLOG BUT I WAS TRYING TO EXECUTE SCD METHOD-2 THEN I COULD NOT OVERCOME SOME ERRORS IN EXECUTE SQL TASK IN UPDATE COMMEND CAN YOU HELP ME OR ELSE WE SHOULD PASS ANY PARAMETER MAPPING
ReplyDeleteGreat post, thanks!
ReplyDelete