Best way for incremental load in ssis

问题:

I am getting 600,000 rows daily from my source and I need to dump them into the SQL Server destination, which would be an incremental load.

Now, as the destination table size is likely to be increase day by day which would be the best approach for the incremental load. I have few options in my mind:

1. Lookup Task
2. Merge Join

3. SCD

   etc..

Please suggest me the best option which will perform well in incremental load.

回答1:

Look at Andy Leonard's excellent Stairway to Integration Services series or Todd McDermid's videos on how to use the free SSIS Dimension Merge SCD component Both will address how to do it right far better than I could enumerate in this box.

回答2:

Merge join is a huge performance problem as it requires sorting of all records upfront and should not be used for this.

We process many multimillion record files daily and generally place them in a staging table and do a hash compare to the data in our Change data tracking tables to see if the data is different from what is on prod and then only load the new ones or ones that are different. Because we do the comparison outside of our production database, we have very little impact on prod becasue uinstead of checking millions of records against prod, we are only dealing with the 247 that it actually needs to have. In fact for our busiest server, all this processing happens on a separate server except for the last step that goes to prod.

回答3:

if you only need to insert them, it doesnt actually matter. if you need to check something like, if exists, update else insert, I suggest creating a oleDbSource where you query your 600.000 rows and check if they exist with a lookup task on the existing datasource. Since the existing datasource is (or tend to be) HUGE, be careful with the way you configure the caching mode. i would go with partial cache with some memory limit ordered by the ID you are looking up (this detais is very important based on the way the caching works)