December 19, 2014 at 4:15 pm
I've been asked to edit an existing SSIS job importing Data from Oracle 11g to SQL 2012. When I try to connect to the Oracle Connection Manager, I get ORA-12154 (which I read is a very generic connection error) TNS:could not resolve the connect identifier specified.
The Oracle people here tell me my TNS file is correct, but they don't know enough about SSIS to help me beyond that. I can also make a successful connection to Orcacle in ODBC. They suggesting I uninstall & reinstall Oracle client, which I've done with their help. Now I'm back at the same place - same error!
What next?
December 20, 2014 at 9:30 am
TNSNames..... I feel bad for you. There are a couple things to try.
-Make sure tnsping works.
-The tnsnames.ora file needs to go in the network/admin folder. If it's in another location, it probably won't be found.
-Make sure the ORACLE_HOME path is set correctly.
Of course, if ODBC is working, then these are probably set up correctly. There are two other things to check for:
-Some searching revealed that SSIS might be running in 32-bit mode instead of 64-bit. If this is the case, then the 32-bit Oracle client will have to be installed and configured.
-If all else fails, you can try the Microsoft Connector for Oracle. 1.2 will work on 2008 and 2.0 will work on 2012. I haven't used it, but it's supposed to make integration with Oracle via SSIS easier.
November 14, 2016 at 7:06 am
crk5070 (12/20/2014)
TNSNames..... I feel bad for you. There are a couple things to try.-Make sure tnsping works.
-The tnsnames.ora file needs to go in the network/admin folder. If it's in another location, it probably won't be found.
-Make sure the ORACLE_HOME path is set correctly.
Of course, if ODBC is working, then these are probably set up correctly. There are two other things to check for:
-Some searching revealed that SSIS might be running in 32-bit mode instead of 64-bit. If this is the case, then the 32-bit Oracle client will have to be installed and configured.
-If all else fails, you can try the Microsoft Connector for Oracle. 1.2 will work on 2008 and 2.0 will work on 2012. I haven't used it, but it's supposed to make integration with Oracle via SSIS easier.
To add to this list you Need to define the TNS_ADMIN System variable with the path to your TNSNAMES.ORA otherwise it definately won't be found. That applies even if the file is located in ?/Network/admin on the Client.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply