Sunday, April 7, 2013

SSIS : Method 3- Using Execute SQL Task and look up task again instead of SCD (Slowly Changing Dimension)

Mehtod 3: Using Execute SQL Task  and look up task again instead of SCD (Slowly Changing Dimension)

Here the concept is , that if there are 600 columns as I told, was in my scenario, then to write the update command for all the column is an headache , and also , it may cause error also several times while mapping the columns from dimDest to ‘tmpdimDest ‘ table
In such case for an urgent solution, we can go with this third method, which is as follows:
a.       We will first delete all the rows from ‘dimDest’ for which Business Key; the rows are present in ‘tmpdimDest’ table. This we will do through EST.
b.      We will simply load ‘dimDest’ from ‘tmpdimDest’ table.
This is the simplest method

Here EST uses the query for deleting the matching records from dimDest as below
Delete dD from dimDest Dd, tmpdimDest tdD
 Where Dd.BusinessKey=tdD.BusinessKey

The DFT is as follows, where
OLeDb Source is configured with ‘tmpdimDest’ table
 Oledb destination is configured with ‘dimDest’ table
This way you can keep yourself away from the headache. 

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.

No comments:

Post a Comment