How do I transfer only new records between two different databases (ie, Oracle and MSSQL) using SSIS?

advertisements

Do you know how to transfer only new records between two different databases (ie. Oracle and MSSQL) using SSIS? There is no problem transfering new data only between two tables in the same database and server, but is this possible to do such operation between completely different servers and databases?

Ps. I know about solution using Lookup but it is not very efficient if anybody needs to check and add a lot of records (50k and more) several times per day. I would like to operate with new data only.


You have several options:

Timestamp based solution

If you have a column which stores the insertation time in the source system, you can select only the new records created since the last load. With the same logic, you can transfer modified records too, just mark the records with the timestamp value when it change.

Sequence based solution

If there is a sequence in the source table, you can load the new records based on that sequence. Query the last value from the destination system, then load avarything which is larger than that value.

CDC based solution

If you have CDC (Change Data Capture) in your source system, you can track the changes and you can load them based on the CDC entries.

Full load

This is the most resource hungry solution: you have to copy all data from the source to the destination. If you do not have any column which marks the new records, you should use this solution.

You have several options to achieve this:

  • TRUNCATE the destination table and reload it from source
  • Use a Lookup component to determine which records are missing
  • Load all data from source to a temporary table and write a query which retrieves the new/changed records.

Summary

If you have at least one column, which marks the new/modified records, you can use it to implement a differential/incremental load with SSIS. If you do not have any clue, which columns/rows are changed, you have to load (or at least query) all of them.

There is no solution which enables a one-query (INSERT .. SELECT) solution using multiple servers without transferring all data. (Please note, that a multi-server query using Linked Servers are transfers the data from the source system).