Oracle connector is not working IN VS2019

Koti Raavi 21 Reputation points
2022-02-02T09:24:47.697+00:00

170539-oracle-connector.pngHello There,

I'm trying to connect oracle through SSIS , installed Microsoft connecter from below mention link

https://www.microsoft.com/en-us/download/details.aspx?id=58228

Here is the error I'm getting. Attached images also for reference.

There was an error trying to establish an Oracle connection with the database server. Please do help me out to fix it. Thank you!

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,449 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
490 questions
Accepted answer
  1. Yitzhak Khabinsky 24,936 Reputation points
    2022-02-02T15:57:16.637+00:00

    Hi @Koti Raavi ,

    Microsoft Oracle connector doesn't require Oracle client installation.
    It is one of its many benefits.

    You should try to use what is called EzConnect format: [//]host[:port][/service_name]
    You need to put it into the TNS service name field.

    It is documented here: oracle-connection-manager

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sun, Christine Q 6 Reputation points
    2022-06-21T21:33:05.68+00:00

    I went to a similar issue, Microsoft connector for Oracle with TNSName only works on 32bit, but not on 64 bit inside or outside SSDT by default.
    Did some investigations and research, was able to make it work.

    Here are my environment:
    SSIS2019 Enterprise Edition Version at: 15.0.4198.2
    SSDT2019 Community Edition 16.11.32602.291
    MicrosoftSSISOracleConnector-15.0.2000.215_amd64
    Oracle ODBC driver shared library-sqora32.dll-19.0.0 (installed with Oracle 19 installation -- both oledb/odbc 32 and 64 installed)
    Window 2019 standard

    Solution:
    Running within SSDT, from property window of "Oracle Connection Manager" that is created from "Oracle Source/Oracle Destination", set up both OracleHome, OracleHome64 accordingly per your Oracle installation
    Mine:
    OracleHome =C:\oracle32\product\19.0.0\client_1
    OracleHome64=C:\oracle\product\19.0.0\client_1
    Upon setup both homes, the package run successfully within SSDT under both 32 and 64 runtime.

    Running outside SSDT with SSISDB store procedure (we have a powershell script to wrap up the SSISDB SP, ran from cmd prompt)
    Need Setup ORACLE_HOME and ORACLE_HOME64 ( including 2 new Environment variables(system); Also in addition to the path added from the original Oracle client installation, add 2 new system path accordingly).
    Restart the cmd prompt, or restart the window to make the changes effective.

    Setup ORACLE_HOME and ORACLE_HOME64
    2 NewEnvironment Variables(System ):
    Add the ORACLE_HOME variable to the New System Variable box, then click OK.
    For example: ORACLE_HOME=C:\oracle32\product\19.0.0\client_1
    Add the ORACLE_HOME variable to the New System Variable box, then click OK.
    For example: ORACLE_HOME64=C:\oracle\product\19.0.0\client_1

    2 new paths added to System Path, make them to the beginning of the system "path"
    %ORACLE_HOME64%\bin;
    %ORACLE_HOME%\bin;

    detail setup Oracle Home , please refer to https://www.ibm.com/docs/en/opw/8.0.0?topic=odci-setting-oracle-home-environment-variable-openpages-application-servers.
    Upon the above setup, the package can be run with both 32bit and 64 bit from cmd prompt.

    0 comments