1

Our ERP is using an Oracle database while our BI system is based on SQL Server. We need to get the changed data in Oracle and copy it in our staging area.

At the moment we get the changed rows by comparing Oracle ROWNUM columns and copy the data with "Microsoft Connectors for Oracle by Attunity" in SSIS. Our problem is that we have to modify the packages by hand to add dynamic queries and it's very inconvenient.

We use this connector because it's easy to create tables on SQL Server with the correct data type based on the oracle source and the performance is great.

We don't like to use linked servers, at least with the default drivers as we had a lot of trouble (a lot of casting to get data and very bad performance compared to the Attunity product)

I've seen that Attunity offers a product called "CDC for ssis". Does anybody have any experience with this tool? What's the best way to get changed data from Oracle to SQL Server? What's the best way to create tables with correct data types?

Improve this question

1 Answer 1

Reset to default
1

You can synchronize Oracle and SQL Server in many ways. For example:

You can use sql server transactional replication with Oracle as a publisher: https://msdn.microsoft.com/en-us/library/ms152481.aspx

You can use Oracle GOldengate (very expensive): http://www.oracle.com/technetwork/articles/datawarehouse/oracle-sqlserver-goldengate-460262.html

regards Giova

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.