In one of my SSIS 2008 applications, I had to extract data from Oracle which would be processed and dumped to SQL Server. In order to do so, SSIS will need have access to an Oracle Provider for OLE DB. I also needed to do a few simple checks here and there using SQL Plus, so there are two components I have to install to accomplish this, Oracle Data Access Components (ODAC) and Oracle Database 11g Release 2 Client for Microsoft Windows (x64), which can be downloaded from oracle.com:
The first component to install is 32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio
To make Oracle provider work within Visual Studio, use the 32-bit version. For deployment on production though, you can choose between 32-bit or 64-bit ODAC.
For some reason, after I installed the 32-bit ODAC, I didn’t find tnsping or SQL*Plus (it turned out SQL*Plus was installed in the C:\oracle\product\11.2.0\client_1 directory, except there was no shortcut created on Windows’ Start Menu Programs List).
So to add tnsping, I installed the Oracle client and chose a different home directory, ..client_2 (but the same Oracle base directory “C:\oracle”).
After the installation, if you open your SSIS package, you can pick Native OLE DBOracle Provider for OLE DB from SSIS Data Source Designer.
As a result of this you will have two installations of SQL*Plus in C:\oracle\product\11.2.0\client_1 and C:\oracle\product\11.2.0\client_2\bin.
Just make sure both directories are referenced in the PATH Environment Variables setting.
Downloads
Oracle Instant Client Downloads
32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio
64-bit Oracle Data Access Components (ODAC) Downloads
Further Reading
How to Build SSIS Package for Different SQL Server Version with Visual Studio 2017 and SSDT
How to Create SSIS Package in Visual Studio 2017
Oracle and Visual Studio 2012 Quickstart
