Oracle and SQL server connection in SSIS: Best provider

Recently, I have been working in a project where I had to make an SSIS package to connect to an Oracle server and load data to a SQL server. After searching a lot and getting through a lot, I figured out the best provider and client for a successful connection. Take note from the following points:

1. In the past, we needed to install a provider as well as an oracle client for making a connection to Oracle from Visual studio. But, these days, Oracle provider package includes a client itself. So, all you had to do is to download the latest provider package from oracle website.

2. You will see Microsoft providers for Oracle in SQL Server Business Intelligence Development Studio. But, they are not good performance-wise and also, Microsoft has stopped giving support for their Oracle provider. So, it’s a big gamble to use them.

3. In the oracle download website, there will be lots of providers. Do not get excited to see a lot of 64bit providers. After a lot of hard work I could not get them working in my 64 bit windows machine. The trick is to download 32 bit provider (ODTwithODAC112021 is the name of the provider that worked for me). Also, it has a OUI (Oracle Universal Interface), whereas 64 bit providers have DOS command based installation. So, conclusion is, just download 32 bit providers irrespective of your machine/OS configuration.

4. If you face any problem in the installation, right click on the setup file and run it in compatibility mode. I tested the provider (ODTwithODAC112021) with Windows XP (32 bit, 64 bit), Windows Server 2008 (64bit) and also with Windows 7 (64bit).

Advertisements

About Zahid

Team Lead at IMS Health (www.imshealth.com). A part-time consultant and trainer. Fields of expertise are ETL, BI Reporting (Microstrategy, Excel, ASP.NET Dashboards etc.) Data Warehouse, OLAP Cube, MDX etc.
This entry was posted in SQL, Database, SSIS (SQL Server Integration Service). Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s