Share via

Migrating SSIS from using Microsoft Connector for Oracle to ADO.NET

OMAR SALEK 20 Reputation points
2025-10-08T18:39:59.54+00:00

From another post : With Microsoft's notice back in January that SSIS in SQL Server 2025 no longer being supported, we're looking to move from the Microsoft Connector for Oracle (prev known as Attunity) over to the ADO.NET source and destinations that Microsoft suggested in https://www.microsoft.com/en-us/sql-server/blog/2025/01/21/sql-server-integration-services-ssis-microsoft-connector-for-oracle-deprecation/

Using the Microsoft connector for Oracle destination in sql server 2019, we can load approx 2.2 million in approximately few seconds. When using the ADO.NET Destination using the OracleClient Data Provider, I can't get the same data to load any faster than 20 minutes.

From the ADO.NET Destination Editor, I have the "Use Bulk Insert when possible", however I haven't seen a difference if it's enabled or not. I've also tried changing the Destination BatchSize, DefaultBufferMaxRows & DefaultBufferSize values 10, 100, 1000 & 10000 times the default.

Am I missing something basic, or is the ADO.NET Destination using Oracle not very performant?

SQL Server Integration Services
0 comments
Answer accepted by question author
  1. Lakshmi Narayana Garikapati 470 Reputation points Microsoft External Staff Moderator
    2025-10-09T08:59:04.3333333+00:00

    Hi @OMAR SALEK ,

    Thanks for reaching out SQL Forum.

    You’re not missing anything the performance gap you’re seeing is expected.

    The Use Bulk Insert, when possible, option in the ADO.NET Destination only applies to ADO.NET providers that return a SqlConnection object (SQL Server). For Oracle connections, this flag has no effect because the Oracle ADO.NET providers don’t expose a compatible bulk insert interface.

    That means when you switch from the Microsoft Connector for Oracle (Attunity) to ADO.NET, SSIS no longer uses Oracle’s native direct-path or array binding APIs it just executes standard parameterized inserts in small batches, which is why your load drops from seconds to minutes.

     

    If you still need near bulk performance, you have a few options:

    • Use ODP.NET (Oracle Managed Data Access) with array binding or OracleBulkCopy through a Script Component.
    • Stage data in flat files and load with SQL*Loader on the Oracle side.
    • Or migrate to Azure / Fabric Data Factory, whose Oracle connector supports optimized bulk paths.

    https://free.blessedness.top/en-us/sql/integration-services/load-data-to-from-oracle-generic?view=sql-server-ver17

    https://free.blessedness.top/en-us/sql/integration-services/data-flow/oracle-destination?view=sql-server-ver17
    https://free.blessedness.top/en-us/sql/integration-services/data-flow/ado-net-destination?view=sql-server-ver17

    Best Regards,Lakshmi.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 27,006 Reputation points
    2025-10-09T15:09:10.8033333+00:00

    Hi @OMAR SALEK,

    Microsoft Connector for Oracle has nothing to do with Attunity. In reality, it is based on the Progress DataDirect Oracle Wire Protocol ODBC driver. It is arguably the best driver performance wise for Oracle db available on planet Earth. Anything else is much slower including Oracle ODP.NET drivers directly from Oracle.

    Microsoft was licensing that ODBC driver for SSIS.

    Unfortunately, it is a real disservice that Microsoft doesn't want to continue to pay for that license anymore in SQL Server 2025 onwards.

    You can buy a license for that driver yourself here: https://www.progress.com/odbc/oracle-database

    Additional benefits of it:

    • No need to install and maintain Oracle Client!!!
    • No need in TNS files.
    • It supports Oracle Easy Connect format for connection strings.
    0 comments

Your answer

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