This document will brief on what is required for an Oracle OLEDB connection to function properly on your local machine or a server .
Software Required:
1. Oracle_client.
Remark :
a. It is really, really important to install 32 bits Oracle_client .
b. Place TNSNAMES.ORA inside folder C:\app\********\product\11.2.0\client_1\network\admin( This is for local machine, server location may vary as to on which drive Oracle is installed).
For more Information as to where to obtain tnsnames.ora file contact your tech lead or search in fileshare of your company.
STEPS:
1. After installation of above software you will find a new OLEDB connection inside connection manager of SSIS . i.e. "Oracle Provider for OLEDB".
Remark : This OLEDB is a 32 bit version so you cannot run package by BIDS until you make "Run64BitRuntime" in project property to "false".
2. Test connection by inserting credentials and if it does not work then there is some issue with tnsnames.ora file . Please verify if you have placed it in right place and if the credentials are correct .
If the above steps are followed then you must be able to work on package having oracle connection.