1. To be able to do it, we need to install Oracle client on the server where SQL server is installed. We have a windows 2008 R2 server and I installed Oracle 12g client on it.
2. Copy the tnsnames.ora file over. Since Oracle 12g is no longer use tns names, I just copy the file to the oracle installation directory and set system variable TNS_ADMIN to point to it.
3. Restart windows server (I’m not sure it is necessary).
4. Launch SQL Server Management Studio, select the database you want data to be imported to and then select Task/Import Data.
5. In the next screen, choose “.Net Framework Data Provider for Oracle” and enter the SID (CONNECT_DATA) name of your database into the Data Source field.
6. Enter username/password and you should be able to connect. Since our company only allows access to the oracle database from certain IPs, I have to ask our Unix admin to update the route table; otherwise I’ll get connection timeout.