Hi tom, greetings. Recently I got a requirement for syncing Oracle data to sql server for few tables with fewer columns with different names. Some of them have foreign-key relations and constraints. I just started learning SSIS for the purpose and I am clouded with many doubts regarding how to tackle bad data and error loggings. Could you please suggest a way to situation where I need to copy data from oracle to sql server even for tables with parent child relations, bad data violating constraints and skipping error records and logging error records. I cannot drop database constraints more over this process will be running for every 10 minutes as data changes will be minor and fewer tables I need to lookup.
Hoping to get positive response from you.
Regards masit.
For a complete and robust solution, Goldengate is probably the best, but of course, requires licensing.
If you are going to roll your own, then you can use the gateway products
https://www.oracle.com/technetwork/database/gateways/index.html or if you want to do it as cost-efficiently as possible, you can use the heterogenous access, which basically lets you access SQL Server via ODBC.
Some notes on that are here
https://asktom.oracle.com/pls/asktom/asktom.search?tag=connectivity-to-sql-server But when you head down this route, basically you becomes responsible for managing all of the data relationships etc, for example, you need to ensure you get/update parent table rows before child tables, to ensure that foreign keys are kept in sync. It's a lot of manual coding, but it can be done.