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?