What is the fastest driver for connecting to an oracle database from SQL Server Integration Services (SSIS)

Question

What is the fastest driver for connecting to an oracle database from SQL Server Integration Services (SSIS) ?
Will we still need to have oracle client installed (with a tnsnames .ora file) etc.

We are currently using SSIS 2012 - but could upgrade
Understand that the oracle database is at least version 11 or later.

We are currently using the Microsoft oracle driver (and have the oracle client - tnsnames.ora - installed on the SSIS server). We have been running with this for a long time , but this driver seems fairly slow, and is 32 bit - understand that when deployed the package has to run in 32 bit mode.

We are using SSDT based on visual studio 2015.

Any help gratefully received.
Kind regards

Answer 1

Hi @Greg Booth ,

Precondition: your SSIS run-time environment should be SQL Server 2019 Enterprise edition.

SSIS 2019 comes with Microsoft Connector for Oracle V1.0
It is very fast (using the best 3rd party ODBC driver behind the scenes) and it has a fantastic bonus - no need to install Oracle client.

You need to have the following installed on a dev. machine:

• VS2019, latest build
• SQL Server Integration Services Projects extention.

Answer 2

The MS-provided driver in .NET has been deprecated for years. In .NET the recommended driver is the OPD.NET driver (which still requires the Oracle client). But to use it you'd need to use the ADO.NET driver when connecting to the Oracle database in Connection Manager. This driver gives you access to the .NET drivers of which Oracle is provided. This is discussed here.

For newer .NET code it is generally recommended that you use the Oracle Managed Driver instead. The aforementioned article mentions that you should consider using it instead but I haven't tried to use it with SSIS so I don't know whether it works or not. The advantage is that it is managed only version so you don't need to install the Oracle client anymore. However it isn't as feature rich as ODP.NET and it requires newer versions of Oracle so that may not work for you. If you do use the OMD then you can configure everything without an ORA file if you want.

Whether the OMD, ODP.NET or Oracle Driver that is from the Oracle client is the fastest is going to depend upon what you're doing. You should test all three to decide. In their the native Oracle driver should be the fastest as it is a direct wrapper around the network calls. However that also involves other native calls that may be slower than using .NET. If you're planning to use the ADO.NET driver then ODP.NET is going to probably be slower as you are going to the ADO.NET driver to the ODP.NET wrapper to the native Oracle driver and then out to the network. OMD is going to be faster than this as it skips the native driver but has the limitations mentioned earlier.

Answer 3

Hi @Greg Booth ,

Microsoft Connector for Oracle enables the ability to export data from and load data into Oracle data source in an SSIS package.

Version support
The following Microsoft SQL Server products are supported by Microsoft Connector for Oracle:

Since SQL Server 2019 CU1
SQL Server Data Tools (SSDT) 15.9.3 or later for Visual Studio 2017
Microsoft SQL Server Data Tools (SSDT) for Visual Studio 2019
The following Oracle database versions of data source are supported

Details you may refer: oracle-connector

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October