1

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, we can load approx 2.2 million records (varchar2(40), numeric) in approximately 10 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?

Improve this question
1
  • I’m facing the same issue, I noticed that the ADO.NET connector is noticeably slower compared to the Oracle connector 2019 . However, I’m considering switching to ADO.NET because the current Oracle connector fails whenever the Fast Load option is enabled, returning the following error: ORA-26095: unprocessed stream data exists. Commented Oct 7 at 11:11

1 Answer 1

Reset to default
2

Response to your question from Microsoft sql form :

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:

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