AcquireConnection method call to the connection manager Oracle Connector failed

1

I have setup the connection manager from SSIS to Oracle as:

enter image description here

In the Oracle source:

enter image description here

I can preview the data successfully: enter image description here

However, upon attempting to execute the packge, the erorr appears as:

SSIS package "C:\Users\kakah\Documents\Visual Studio 2015\Projects\TestExcel\TestExcel\INV_MTL_SYSTEM_ITEMS_TL.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Warning: 0x802092A7 at Data Flow Task, OLE DB Destination [17]: Truncation may occur due to inserting data from data flow column "DESCRIPTION" with a length of 600 to database column "DESCRIPTION" with a length of 150.
Warning: 0x802092A7 at Data Flow Task, OLE DB Destination [17]: Truncation may occur due to inserting data from data flow column "LONG_DESCRIPTION" with a length of 2000 to database column "LONG_DESCRIPTION" with a length of 150.
Error: 0x2C0 at INV_MTL_SYSTEM_ITEMS_TL: OCI error encountered. ORA-12154: TNS:could not resolve the connect identifier specified
Error: 0x20F at Data Flow Task, Oracle Source [62]: The AcquireConnection method call to the connection manager Oracle Connector failed with error code 0x80004005.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: Oracle Source failed validation and returned error code 0x80004005.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "C:\Users\kakah\Documents\Visual Studio 2015\Projects\TestExcel\TestExcel\INV_MTL_SYSTEM_ITEMS_TL.dtsx" finished: Failure.

What could I be missing? How can this be resolved?

oracle
ssis
asked on Stack Overflow Nov 16, 2016 by Sauron

3 Answers

0

What exactly do you mean by executing the package? Execute it from BIDS/SSDT or SQL Server agent?

Are there any configuration files/dynamic configurations that you are using? From what it appears, the tnsnames.ora file that your connection manager is using at run time has bad connection information.

answered on Stack Overflow Nov 17, 2016 by rvphx
0

SSDT 2017 connecting to Oracle 9i.

Issue : on my windows 10 laptop I had installed 10G 32 Bit and 11G 64 bit client installed

Resolution : Remove all the clients and install only one version of Client 11G both 32 and 64 bit client and SSIS worked like a charm

answered on Stack Overflow Jun 27, 2019 by Darren Quadros
0

If you right click on the project and go to properties --> Configuration Properties --> Debugging, did you make sure Run64BitRuntime is False? Sometimes that gets toggles to True and will break it.

answered on Stack Overflow Jan 13, 2020 by Patrick Perez

User contributions licensed under CC BY-SA 3.0