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).
Zahid's blog on Data Engineering and BI 