This is part III of my continuing series on working with Oracle for SQL Server pros …
In this Oracle posting, I’ll focus on data integration as part of an ETL strategy where you will build a SQL Server data warehouse from Oracle data sources. This is a very common practice and allows you to take advantage of the awesome SQL Server data integration and ETL capabilities that you essentially get for free as they come with the SQL Server product by way of SSIS (SQL Server Integration Services).
What I’ll do is point out to you a few links to dive deeper, some gotchas, best practices and approaches that I have taken in the past that were successful. If you are not careful about your approach to integrating Oracle databases into SQL Server databases, you may find yourself quickly falling victim to degrading performance that fails to meet your most basic SLAs and data management needs.
The most obvious and easiest mechanism that I find to connect to Oracle data source from SSIS is to use the Microsoft OLE DB Provider to Oracle (see pic below). There are also many other providers besides Microsoft for Oracle. But with SQL Server, you get this OLEDB adapter from Microsoft out of the box. I’ve used it many times, it works just fine. There is a good paper on SQL CAT here which you can use to see some of the tests and timings recorded and published by the Microsoft SQL customer advisory team when connecting to Oracle from SQL Server, it’s very helpful for you as you set your service level agreements with your business users. A few things to watch out for include that you must know the Oracle database network name that is being broadcast from the TNSNAMES configuration on your Oracle database sources. If you do not have access to the Oracle servers, make sure that you’ve collected a read-only login to the database, the TNS NAME and the server host name (physical or virtual).
OLEDB, however, is not generally the fastest mechanism to use to access data from Oracle. That being said, not every use case where you utilize SQL Server either as a DW or for an application that needs data from Oracle, requires super-fast access to the Oracle data source. For instance, an ETL job that runs nightly may have a large window with which it can use to pull data from, not requiring lightening-speed data access to the Oracle sources.
However, if you need to access Oracle faster than OLEDB, SQL Server 2008 ships with a set of high-speed adapters for Oracle from Attunity that you can use with SQL Server. To use the Attunity-provided Oracle adapter with SSIS, go to this Microsoft download link here. Once you add the Attunity drivers, you can then select those from your SSIS data source or data target to take advantage of the improved data movement speeds.