Oracle connection fails in debugging

Question

Hello,

I have an SSIS package in VS2005. It has an Oracle connection that works when I test it, works when I use Preview data in the OLE DB Source object, but fails when I run Debugging. Error says couldn't resolve the TNS (see full text below).

Running VS2005 on Windows 7 64 bit. Oracle DB is 11g. I have applied the parens (x86) in path workarounds for VS2005. 64BitRuntime debugging is set to False. All the .ora files (LDAP, SQL TNSNAMES) are correct and have been tested (using tnsping). No other connections to different DB types (i.e SQL, etc.) are failing.  My ODBC file connections (32 and 64 bit) all work.

Have 'googled' the various solutions, none have helped. Kinda at a loss now. Any suggestions?

Thanks,

Chris

----------------------

<<Error: 0xC0202009 at DailyDocDetail-InProgress package, Connection manager "PSDW.wipreport 1": SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.  Source: "OraOLEDB"  Hresult: 0x80004005  Description: "ORA-12154: TNS:could not resolve the connect identifier specified".

Error: 0xC020801C at Req D_Data Flow Task_WIPTrackingData_from_ORACLE_to_SQLServer, PSDW [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "PSDW.wipreport 1" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error: 0xC0047017 at Req D_Data Flow Task_WIPTrackingData_from_ORACLE_to_SQLServer, DTS.Pipeline: component "PSDW" (1) failed validation and returned error code 0xC020801C.

Error: 0xC004700C at Req D_Data Flow Task_WIPTrackingData_from_ORACLE_to_SQLServer, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Req D_Data Flow Task_WIPTrackingData_from_ORACLE_to_SQLServer: There were errors during task validation.
SSIS package "DailyDoc_InProgressPackage.dtsx" finished: Failure.>>

Answer 1

Hi,

Welcome to the MSDN forum!

I'm trying to involve some senior engineers into this issue and it will take some time. Your patience will be greatly appreciated.

Regards.

---

<THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
Thanks
MSDN Community Support

Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

Answer 2

The error is coming from Oracle-grown OLEDB Provider. You need to contact Oracle support or forums as we do not have access to Oracle's source code: (https://forums.oracle.com/activity).

Please note: Microsoft-grown OLEDB Providers and ODBC Drivers for Oracle are deprecated and no longer kept up-to-date. You need to use Oracle-grown drivers and providers to connect to Oracle databases.

Answer 3

Hello,

An update on this issue.  I removed all Oracle programs from the laptop (both 32 and 64 bit). Then I installed only the 32 bit Oracle programs. The problem is solved now.

Note: I start VS2005 as admin from a shortcut that does not contain parens in the path. e.g. C:\Program Files x86\...

Regards,

Chris