Oracle, SSIS, and CDC

  • I have an Oracle source table and a SQL Server 2008 destination table. The source and destination tables have the same columns. The tables include, but are not limited to, these columns:

    CREATION_DATE

    LAST_UPDATE_DATE

    LAST_UPDATED_BY

    I would like to have an SSIS package that only checks for changes in the source (Oracle) table. Whatever inserts, updates, or deletes happened in the source table should also happen in the destination table.

    What approaches can I take to make this happen? I see that Attunity has a CDC for SSIS solution, but it seems a little pricey for our humble shop. Are there any alternatives? If I have to "hand code" this in SSIS, what is the best approach?

  • What version of Oracle do you have? Oracle has native CDC since version 9i I believe. You could enable Oracle CDC on the source table and simply create an SSIS package that does a conditional split based on the updates, inserts and deletions found on the tracking table.

    You have several other options. These are some that I have used or seen used depending on the specific case:

    1) You could use the Merge transformation in SSIS or a Merge Join statement. Easily identify updated, inserted, deleted data.

    2) Use the Execute SQL task and execute a SELECT Statement with WHERE EXISTS or WHERE NOT EXISTS depending on your logic. With this approach you identify quickly new data inserted.

    3) Create a hash with the columns you are interested using hashbytes (T-SQL) for source and destination data. With his approach you quickly identify the rows that don't match between source and destination table.

    4) Use the lookup transformation as described here.

    I hope these helps!

    Jose Chinchilla aka @sqljoe

  • Thank you for your help. Wouldn't an Execute SQL Task on an Oracle table require a linked server? Also, I've been told that I will have very limited access to the Oracle 11g database where the source tables reside, so I probably won't be able to use Oracle's CDC features.

  • You could request CDC to be enabled on the table you need to track changes.

    You can also request for it to be replicated to a SQL Server database and enable Change Data Tracking on your SQL Server instance for that table. This requires Enterprise or Developer edition though.

    Simply request replication to a SQL Server instance with SSIS and do your T-SQL/SSIS there.

    If none of these options is feasible, then I would recommend doing some of the strategies described, Merge, Lookup or Exists.

    How many rows will you be processing +/-? If they allow it and there aren't that many rows, you can pull the data to CSV file.

Viewing 4 posts - 1 through 3 (of 3 total)

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