• If the destination is a SQL Server db, you cannot beat a MERGE statement in T-SQL for performance.

    As for the Oracle thing, SSIS would be your best bet. Do a lookup to your destination and then compare it to your data using a conditional split component (You would have outputs for new, changed and deleted). Connect the outputs to either a destination component (for the new ones) or a sql component (for the updates/deletes). This is a bit of a row by row solution but works marginally better than the out of the box SCD component. There is also the Kimball SCD component which is almost as quick as MERGE, but it involves third party software and sometimes you will be governed by a policy that doesn't allow this. As it's oracle you will need some kind of driver on your machine in order to read/write data.

    Hope this helps 🙂


    I'm on LinkedIn