solution @KotiRaavi-6819 · Feb 02 2022 --Microsoft Connector for Oracle work with TNSName?

Sun, Christine Q 6 Reputation points
2022-06-21T21:17:20.113+00:00

I did not see the solution @Koti Raavi · Feb 02 2022 at 8:03 AM, but got into the same issue, was able to run it with 32bit within SSDT, but not 64.
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.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,641 questions
0 comments No comments
{count} vote

1 answer

Sort by: Most helpful
  1. Yitzhak Khabinsky 26,376 Reputation points
    2022-06-21T21:53:33.793+00:00

    Hi @Sun, Christine Q ,

    An excerpt from my previous answer:

    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]

    No need to install twice Oracle client, i.e. 32-bit and/or 64-bit edition.
    No need to 'pollute' your servers. Less maintenance.
    And Microsoft Oracle connector works well in the 64-bit server environment.

    Also, no need in SSDT. It is just for VS 2017 and earlier.
    VS2019 is using SQL Server Integration Services Projects extension.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.