Anatomy of an Incremental Load

  • 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

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

    Thanks!:-)

  • 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

  • Solid!:cool:

  • Love your tag 🙂

  • 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 🙂

  • 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

  • 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?

  • 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

  • @Andy

    That was simply Amazing explanation about Incremental Load using SSIS.

    Thanks and Appreciated for all your Help.

    Bheeshma K

  • 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:

  • 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

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

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