Well it is looking like the SSIS method is not going to work either sigh 😉

Apparently there is a bug when using a Lookup transform on DB2 data when it is a Decimal Data Type. Unfortunately several columns In our source system (DB2) are Decimal, and many of them would need to be compared making this bug rather prohibitive! However, I would still like to thank you Robert, as I did read the stairway to SSIS series and learned quite a bit and was successful at incremental loads on several tables, there are just some major ones that wont work because of the data conversion mentioned above!

So it's back to the OPENQUERY method. Hopefully Jeff can chime back in here regarding some more effective methods 🙂

I have also revised my insertion and update methods based on the entry and change dates (I had forgotten to do so before) and that has sped things up considerably. However, the deletion process still takes forever as it has to search the whole source table for entries that are not there in the destination table which takes just as long to load the data.