I have to sync data between Oracle and SQL Server database tables. And I'm actually looking for some advice on which technologies to use or how to address this.
At the moment I'm doing the Oracle → SQL Server direction.
Oracle table inserts, updates and deletes are written to temporary tables and a console application picks it up. The downside of this solution is that it's based on scheduling of the console application and because of that there is some delay of syncing (Task Scheduler on console application). A perfect solution would be to sync the Oracle data changes in real-time to SQL Server.
I was thinking about making a Data Flow using SQL Server Integration Services Package. But how can I call this SSIS package when a data change happens on Oracle?
My question is: can we write data to a temporary table using a trigger, but can we also execute a SSIS package or a console application at that time (I mean real-time) and not on a scheduled basis?
Thanks for any advice and sorry if I've written something wrong. I'm not a DBA.