Anatomy of an Incremental Load

Question

Anatomy of an Incremental Load

Viewing 12 posts - 91 through 101 (of 101 total)

You must be logged in to reply to this topic. Login to reply

Andy Leonard SSChampion Points: 10134 More actions · Answer 1

Hi Cyril,

There are use cases where Truncate-and-Load will perform better than an Incremental Load.

Keep in mind that SQL Server - and SSIS - performance is impacted by lots of outside factors, starting with network topology and extending to the size and shape of the data itself.

The science of Lookups is a field all by itself, and I barely touched on it in this article. In other writing I talk about Change Detection (SQL Server 2008 Change Data Capture is one mechanism) which offers some relief to some incremental loads. Note this will not solve everything all the time - and if I didn't make that clear earlier allow me to do so now: there are some use cases for which Incremental Loading is *not* the answer. You may very well have hit upon one.

As an engineer, I always recommend testing to see which will perform best. It sounds as if you have performed these tests and found your best solution.

:{> Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

cyril.capistrano SSC Rookie Points: 31 More actions · Answer 2

That was the first post I've ever done and I thank you for your useful response. I might just post some more!

Thanks!:-)

Andy Leonard SSChampion Points: 10134 More actions · Answer 3

Hi Cyril,

I encourage you to not only post in the forums - you should also consider writing about your experiences! Steve Jones is always on the lookout for new material and authors.

:{> Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

cyril.capistrano SSC Rookie Points: 31 More actions · Answer 4

cyril.capistrano

SSC Rookie

Points: 31

June 8, 2010 at 1:09 pm

#1177648

Solid!:cool:

epriddy Old Hand Points: 343 More actions · Answer 5

epriddy

Old Hand

Points: 343

June 24, 2010 at 11:24 am

#1184633

Love your tag 🙂

mhassanshahbaz SSC-Addicted Points: 416 More actions · Answer 6

hi andy!

above examples are working when primary key column data type is int,but not in case of var char, as i have course in course_dim table,what to do with this case??

2)when it found & loaded the new row dwh,next time it again loading that same one along new rows,why?

3)i want to send "new" & changed rows to destination,what to do in this case,describe it too plz

hope to hear from you soon 🙂

FairFunk SSC Eights! Points: 879 More actions · Answer 7

Hi Andy,

Great post, it really helped me out of a bind!

Keep up the good work, as its really hard to find good SSIS tutorial examples.

Cheers

Chris

rockstar283 SSCrazy Points: 2704 More actions · Answer 8

Guys..what is your opinion about indexes..as per my knowledge indexes are heavily used in OLAP..but I generally disable the indexes before loading new data and again enable them back after loading the data..do you think its a good strategy?

Andy Leonard SSChampion Points: 10134 More actions · Answer 9

rockstar283 (6/4/2011)
Guys..what is your opinion about indexes..as per my knowledge indexes are heavily used in OLAP..but I generally disable the indexes before loading new data and again enable them back after loading the data..do you think its a good strategy?

Indexes can slow inserts. If they cause enough pain, I drop them before the load and then re-apply afterwards.

:{>

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

kaalabhairavaa Newbie Points: 9 More actions · Answer 10

@Andy

That was simply Amazing explanation about Incremental Load using SSIS.

Thanks and Appreciated for all your Help.

Bheeshma K

msmithson Ten Centuries Points: 1171 More actions · Answer 11

This was a great article, very straightforward, helpful, and exactly what I needed. Now I can load my data without just truncating and reloading. Thanks!:w00t:

Andy Leonard SSChampion Points: 10134 More actions · Answer 12

msmithson (12/26/2012)
This was a great article, very straightforward, helpful, and exactly what I needed. Now I can load my data without just truncating and reloading. Thanks!:w00t:

Thanks msmithson - I am glad it helped!

:{>

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics