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!