April 19, 2011 at 10:06 pm
Is any one can explain me or how i can configure Checksum Transformation, I am using SCD Transformation to update values but it taking forever so i am using Checksum Transformation, i never used it before, I google it but nothing comes up to help me. Thanks.
April 20, 2011 at 12:12 am
Here is a pretty decent article:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/
(Googled on SSIS checksum transformation. 2nd result)
I would however advise you to just dump the SCD component, it is not peformant at all, which you already have noticed. The problem lies with the OLE DB Command, which performs a single update per row.
It is better to create it manually in the dataflow:
first find out which records from the source are updates or inserts. You can do this in the SQL statement in the OLE DB Source by using a LEFT OUTER JOIN (be sure to place a with(nolock) table hint on the querym, or you could get locking issues), or by using a LOOKUP component, if your destination table isn't too large.
After that, seperate updates from inserts using a conditional split. Write the inserts straight away to the destination table with the OLE DB Destination using the fast load option. Write the updates to a staging table that has the same structure as the destination table. Do a set based update with an EXECUTE SQL Task. UPDATE statement with a JOIN clause. Since you have the surrogate key of the destination table, this join will go really fast.
If you follow these steps, performance will drastically improve. You can still add the checksum if you really want, to tweak performance even further. But in most cases it isn't necessary...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply