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.