Blog Post

SQL Server Integration Services (SSIS) Blog
2 MIN READ

General availability of Microsoft Connector for Oracle

Chunhua's avatar
Chunhua
Icon for Microsoft rankMicrosoft
May 25, 2020
Microsoft Connector for Oracle is now generally available.    Microsoft Connector for Oracle provides compatibility with Attunity Oracle Connector in functionality and performance. Packages desig...
Updated May 25, 2020
Version 1.0
Luc_TREMOLLET's avatar
Luc_TREMOLLET
Copper Contributor
Nov 06, 2024

Hello

I am writing to seek assistance with an issue I am encountering with an SSIS 2019 package running on a SQL Server 2019 instance. The package is designed to transfer data from an Oracle source to a SQL Server destination using the Microsoft Oracle Connector 2019. Under normal circumstances, the package functions perfectly, copying data from Oracle to SQL Server without any issues.

However, I have recently enabled transaction support to manage rollbacks in case of any issues with the Oracle source. Since enabling this feature, I have encountered the following errors:

  1. Error on Oracle Connector Component:
  2. 0xC020801C SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager failed with error code 0xC0014018.
  3. ODBC Connection Transaction Error:
  4. There was an error trying to enlist an ODBC connection in a transaction. The SQLSetConnectAttr failed to set the SQL_ATTR_ENLIST_IN_DTC attribute.

I would greatly appreciate any guidance or solutions you could provide to resolve these errors. Thank you for your time and assistance.

Best regards,

  • MakingEasyLookHard's avatar
    MakingEasyLookHard
    Brass Contributor
    Nov 06, 2024

    You wont like this response but Microsoft has completely screwed up the Oracle drivers in 2019 and above. We couldnt use these specific Microsoft drivers because when they trashed the Attunity ones and rebranded them they no longer supported LDAP connectivity and told us via a support case they did not know when or if they would add that functionality back.

    So we switched gears and just started using an ODAC Oracle ODBC driver which did support LDAP but then we realized that SSIS would not pull all the data but also not fail. Its like it wasnt detecting it hadnt gotten all the data and would just look like it was successful. Obviously a huge issue if you cant trust a package to pull all the data or fail.

    So we are currently working with Microsoft on a paid support issue for the last 3 months trying to get a resolution. They have reproduced it on their side but dont have a fix. It is blocking us from moving our ETL SQL Server off of 2016. So I know it isnt exactly your issue but Oracle driver issues in SQL SSIS >=2019 abound. Good luck!