September 19, 2012 at 2:40 am
Hello Gurus,
Im new to SSIS . Need your help to resolve a performance issue.
I have a dimension table with 120 columns in which 80 columns change frequently and I need to capture the changes. Im using lookup transformation to identify Matched record and unmatched records if it is matched record will do an update and if it is unmatched record then an insert. The table has around half a million records. For initial load the package is taking around 12 minutes and if there any updates it is taking around 30 minutes. The Table has only one primary key. Can anyone please suggest best approach to design the Package?
Thanks
September 19, 2012 at 2:52 am
keka4747 (9/19/2012)
Hello Gurus,Im new to SSIS . Need your help to resolve a performance issue.
I have a dimension table with 120 columns in which 80 columns change frequently and I need to capture the changes. Im using lookup transformation to identify Matched record and unmatched records if it is matched record will do an update and if it is unmatched record then an insert. The table has around half a million records. For initial load the package is taking around 12 minutes and if there any updates it is taking around 30 minutes. The Table has only one primary key. Can anyone please suggest best approach to design the Package?
Thanks
Why not use the Slowly Changing Dimension Data Flow Transformation? It's designed to deal with this kind of problem.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
September 19, 2012 at 2:58 am
Thanks Abu Dina for the reply .
Intially started working with Slowly Changing Dimension Data Flow Transformation but it is took more time so looking for other options
Thanks
Keka
September 19, 2012 at 6:15 am
keka4747 (9/19/2012)
Hello Gurus,Im new to SSIS . Need your help to resolve a performance issue.
I have a dimension table with 120 columns in which 80 columns change frequently and I need to capture the changes. Im using lookup transformation to identify Matched record and unmatched records if it is matched record will do an update and if it is unmatched record then an insert. The table has around half a million records. For initial load the package is taking around 12 minutes and if there any updates it is taking around 30 minutes. The Table has only one primary key. Can anyone please suggest best approach to design the Package?
Thanks
What technique are you using to do the UPDATEs?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply