SSIS 2016 having issue with Oracle Connectivity

Question

SSIS 2016 having issue with Oracle Connectivity

shuxyz

SSC Enthusiast

Points: 175
More actions
September 28, 2019 at 3:40 pm

#3684297

Hi All,
I am working on SSIS upgrade project. I have upgraded SSIS 2008 package to SSIS 2016 using Visual Studio 2015.
All packages have oracle connectivity. As we have a new database server, I have installed Oracle client 11g (64 and 32 bit) and Attunity driver (4.0 and 5.0)
I have deployed the packages in SSISDB and tried to execute. But getting following error
DS_EDC_T is a Microsoft OLE DB Provider for Oracle Connection
Please let me know
1. how to resolve this issue
2. I am missing any setup or software installation
May 12, 2020 at 11:01 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

when installing the oracle client make sure you select the connectivity tools (oledb and ado). by default they are not selected.

also make sure you did install the 64 bit versions of both Oracle and Attunity and also that the ssis packages are set to run in 64bit mode

shuxyz SSC Enthusiast Points: 175 More actions · Answer 2

Thanks for response.

I have done Installation Type : Administrator and there is no option to select oledb/ado.

Does this installation include OLE DB Provider ?

Will installing Oracle Data Access Components (ODAC) for Windows help ?

I get two errors as below

Error:Failed to acquire connection "XXXX". Connection mat not be configures

correctly or you may not have the right permissions on this connection

The requested OLEDB provider MSDAORA.1 is not registered. If the 64-bit

driver is not installed , run the package in 32-bit mode.

An OLEDB record is available. Description : "Class not registered"

sterling3721 SSChasing Mays Points: 658 More actions · Answer 3

You said, "DS_EDC_T is a Microsoft OLE DB Provider for Oracle Connection", I assume it's MSDAORA.1 from your screenshot, this is a very old driver for SQL 2008. Microsoft did not provide this on SQL 2012 or later. So, you need to use "OLE DB provider for Oracle from Oracle(included in Oracle Instant Client, or install Oracle Data Access Components (ODAC) only). I use SQL 2016 Standard, no Attunity driver(for Enterprise only), with ODAC, it works fine. 64bit only is enough for production SQL server 2016 if you run SSIS in 64 bit like me. You also need ODAC 32 bit for debugging on SSDT.