Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mgreen84
Yak Posting Veteran
94 Posts |
 Posted - 2013-10-23 : 18:27:26
|
| 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
52326 Posts |
Posted - 2013-10-24 : 01:55:05
|
| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
mgreen84
Yak Posting Veteran
94 Posts |
Posted - 2013-10-24 : 15:30:50
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-25 : 02:00:21
|
| you're welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
Slowly Changing Dimensions