I've found the Oracle.ManagedDataAccess.Client driver library to be much easier to use and deploy in my .NET applications than the standard ODP.NET 250mb+ Oracle client library.
Is there any way the Managed version of the Oracle driver can be used with SQL Server Integration Services (SSIS)?
I'm using the latest version of the ManagedDataAccess client (121.1.2) and SQL Server 2012.
Yes! I have used both the managed and the un-managed ODP.NET Providers from within SSIS. The trick is to register the providers globally (in the GAC) which will make them visible to consuming applications. The steps below are based on the installation of the Oracle 18.3 32bit and 64bit clients which are available for download here. The steps could be adapted to other client versions (like the nuget release). I am not sure if the nuget download comes with the OraProvCfg.exe utility; but basically it is modifying sections of the MACHINE.CONFIG. The config.bat also runs a bunch of OraProvCfg.exe steps. You should be able to download the full client and run through the steps below to reverse engineer the changes to MACHINE.CONFIG that you could then manually implement. Good luck!
The script assumes that your clients are installed into the following directories:
C:\Oracle\Product\32BIT\18.0.0\client_1\
C:\Oracle\Product\64BIT\18.0.0\client_1\
You will likely need to update the path references below to reflect your installation.
Oracle has decent (albeit not easily discover-able) documentation available here.
Open up an elevated command prompt and execute the commands below, checking each output for success.
Managed 32bit
CD "C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\managed\x86\"
C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\managed\x86\configure true
If using LDAP, you will need to tell the Provider as such and where to look for LDAP.ORA; otherwise you do not need to execute the following statements
C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\managed\x86\OraProvCfg /action:unconfig /product:odpm /frameworkversion:v4.0.30319 /providerpath:"C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\managed\common\Oracle.ManagedDataAccess.dll"
C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\managed\x86\OraProvCfg /action:config /product:odpm /frameworkversion:v4.0.30319 /providerpath:"C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\managed\common\Oracle.ManagedDataAccess.dll" /set:settings\LDAP_ADMIN:"C:\Oracle\Product\32BIT\18.0.0\client_1\network\admin" /set:settings\NAMES.DIRECTORY_PATH:"(LDAP)"
Managed 64bit
CD "C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\managed\x64\"
C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\managed\x64\configure true
If using LDAP, you will need to tell the Provider as such and where to look for LDAP.ORA; otherwise you do not need to execute the following statements
C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\managed\x64\OraProvCfg /action:unconfig /product:odpm /frameworkversion:v4.0.30319 /providerpath:"C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\managed\common\Oracle.ManagedDataAccess.dll"
C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\managed\x64\OraProvCfg /action:config /product:odpm /frameworkversion:v4.0.30319 /providerpath:"C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\managed\common\Oracle.ManagedDataAccess.dll" /set:settings\LDAP_ADMIN:"C:\Oracle\Product\32BIT\18.0.0\client_1\network\admin" /set:settings\NAMES.DIRECTORY_PATH:"(LDAP)"
Un-managed 32bit
CD "C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\bin\2.x"
C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\bin\2.x\OraProvCfg.exe /action:unconfig /product:odp /frameworkversion:v2.0.50727 /providerpath:"C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll"
C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\bin\2.x\OraProvCfg.exe /action:config /product:odp /frameworkversion:v2.0.50727 /providerpath:"C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll"
C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\bin\2.x\OraProvCfg.exe /action:gac /providerpath:"C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll"
CD "C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\bin\4"
C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\bin\4\OraProvCfg.exe /action:unconfig /product:odp /frameworkversion:v4.0.30319 /providerpath:"C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\bin\4\Oracle.DataAccess.dll"
C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\bin\4\OraProvCfg.exe /action:config /product:odp /frameworkversion:v4.0.30319 /providerpath:"C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\bin\4\Oracle.DataAccess.dll"
C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\bin\4\OraProvCfg.exe /action:gac /providerpath:"C:\Oracle\Product\32BIT\18.0.0\client_1\ODP.NET\bin\4\Oracle.DataAccess.dll"
Un-managed 64bit
CD "C:\Oracle\Product\64BIT\18.0.0\client_1\ODP.NET\bin\2.x"
C:\Oracle\Product\64BIT\18.0.0\client_1\ODP.NET\bin\2.x\OraProvCfg.exe /action:unconfig /product:odp /frameworkversion:v2.0.50727 /providerpath:"C:\Oracle\Product\64BIT\18.0.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll"
C:\Oracle\Product\64BIT\18.0.0\client_1\ODP.NET\bin\2.x\OraProvCfg.exe /action:config /product:odp /frameworkversion:v2.0.50727 /providerpath:"C:\Oracle\Product\64BIT\18.0.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll"
C:\Oracle\Product\64BIT\18.0.0\client_1\ODP.NET\bin\2.x\OraProvCfg.exe /action:gac /providerpath:"C:\Oracle\Product\64BIT\18.0.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll"
CD "C:\Oracle\Product\64BIT\18.0.0\client_1\ODP.NET\bin\4"
C:\Oracle\Product\64BIT\18.0.0\client_1\ODP.NET\bin\4\OraProvCfg.exe /action:unconfig /product:odp /frameworkversion:v4.0.30319 /providerpath:"C:\Oracle\Product\64BIT\18.0.0\client_1\ODP.NET\bin\4\Oracle.DataAccess.dll"
C:\Oracle\Product\64BIT\18.0.0\client_1\ODP.NET\bin\4\OraProvCfg.exe /action:config /product:odp /frameworkversion:v4.0.30319 /providerpath:"C:\Oracle\Product\64BIT\18.0.0\client_1\ODP.NET\bin\4\Oracle.DataAccess.dll"
C:\Oracle\Product\64BIT\18.0.0\client_1\ODP.NET\bin\4\OraProvCfg.exe /action:gac /providerpath:"C:\Oracle\Product\64BIT\18.0.0\client_1\ODP.NET\bin\4\Oracle.DataAccess.dll"
After registration of the providers, they will just show up under ADO.NET data providers as depicted below:
