SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Slowly Changing Dimensions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mgreen84
Yak Posting Veteran

62 Posts

Posted - 10/23/2013 :  18:27:26  Show Profile  Reply with Quote
I have a package that was created like 5 years ago, pretty much moving data from one table on server A to the same table over on server B. Currently the SSIS package truncates the date on the table on server B, and then updates it with the imported data from server A's table. This worked fine back then because we were only truncating and inserting about 5,000 records. However now thats increased to about 20,000 records. And I believe this approach is no longer appropriate, and can be done more efficiently. Does anyone know how I can acheive that? I have knowledge of a slowly changing dimensions, but never used. Is that something that could be used here?

Thanks

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/24/2013 :  01:55:05  Show Profile  Reply with Quote
Slowly Changing Dimension Wizard will perform quite poorly for large datasets. A much better approach would be to use lookup task for this. One quetion though is do you need to process all records everyday. In normal scenarios you need to capture only deltas (changes) for the day. For this purpose you'll have an audit column like DateCreated in your table which can be used in your source to get current day changes. Then use lookup task to compare against destination based on Key columns. Then join match output to OLEDBe command to do update and join no match output to OLEDB destination to do INSERT.
A much faster approach would be to bring all the deltas (changes) to a staging table in ServerB and then use Execute sql task to run T-SQL query using INSERT/UPDATE statements or MERGE statement to do UPDATE and INSERTs. This wouls be set based and would be fastest one if staging table is feasible. The staging table has to truncated each time prior to new data load.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mgreen84
Yak Posting Veteran

62 Posts

Posted - 10/24/2013 :  15:30:50  Show Profile  Reply with Quote
Thanks for your response. Yes I have a 'LastUpdated' column that I can use as a delta. I'm gong to try your 2nd option with the staging table. Thanks again for the advice and recommendations.

quote:
Originally posted by visakh16

Slowly Changing Dimension Wizard will perform quite poorly for large datasets. A much better approach would be to use lookup task for this. One quetion though is do you need to process all records everyday. In normal scenarios you need to capture only deltas (changes) for the day. For this purpose you'll have an audit column like DateCreated in your table which can be used in your source to get current day changes. Then use lookup task to compare against destination based on Key columns. Then join match output to OLEDBe command to do update and join no match output to OLEDB destination to do INSERT.
A much faster approach would be to bring all the deltas (changes) to a staging table in ServerB and then use Execute sql task to run T-SQL query using INSERT/UPDATE statements or MERGE statement to do UPDATE and INSERTs. This wouls be set based and would be fastest one if staging table is feasible. The staging table has to truncated each time prior to new data load.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/25/2013 :  02:00:21  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000