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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy