Connecting from SQL Server SSIS to Oracle can be problematic, to say the least! I’m logging them on my blog as I find them. One error message that turned up recently was the following:
cannot resolve connection
This can mean that there is a line missing from the Oracle file called SQLNET.ORA. For some older implementations of Oracle, the Oracle TNSNAMES.ORA file may have the connections defined as .world rather than simply HOST.
This is resolved by ensuring that the SQLNET.ora file contains the appropriate following statement:
names.default_domain = world
Alternatively you can remember to add ‘.world’ to all of your connection strings. The easiest thing, however, is to ensure that the SQLNET.ora file has the ‘.world’ information added.
Hope that helps!
Thank god I dont do Oracle in SSIS anymore. Was a pain in the *** two years ago
Hi Jen,
Thanks for sharing this. I struck this and tried to just edit the sqlnet.ora file, but it didn't work. I had to pepper my tnsnames.ora with .WORLD suffixes. I think this post might explain why:
http://thetendjee.wordpress.com/2007/03/30/ora-12514-tnslistener-does-not-currently-know-of-service-requested-in-connect-descriptor/
I can't beleive it's 2012 and I'm still hacking around .ora files on individual machines …