The Many Oracle Connectors for SSIS

I recently worked with a client who was upgrading and deploying several SSIS projects to a new server. The SSIS packages connected to an Oracle database in various tasks. There were:

  • Data Flows that used the Oracle Source and Oracle Destination
  • Data Flows that used an OLE DB connection to Oracle
  • Execute SQL tasks that connected to Oracle via an ADO.Net connector

We needed to make sure we had the most current version of the drivers required to make the package work.

This turned out to be an adventure involving 3 different drivers and plans to refactor, so I’m documenting some of it here in case it helps someone else.

SSIS Target Versions and Visual Studio Versions

It’s important to understand the relationship between Visual Studio and SSIS target versions. In Visual Studio, we must install an extension to allow us to develop SSIS packages. The version of Visual Studio is different but related to the SSIS target version. Visual Studio 2019 with the 2019 SSIS extension supports SSIS versions 2012 through 2022. Visual Studio 2022 with the 2022 SSIS extension supports SSIS versions 2017 through 2022.

The target version is set in the project configuration properties. This target version should match the version of SSIS running on the server where you plan to deploy the SSIS packages.

Oracle Driver Changes since SSIS 2017

In the olden days (pre-SQL 2019) we used the Attunity Connector to connect to Oracle because it provided the best performance. Attunity was acquired by Qlik in 2019, and Microsoft took over maintaining and distributing the connectors. If you need the Attunity connectors for SSIS versions 2012 – 2017, you will find the links for the downloads here.

For target SQL Server versions 2019 through 2022, we now have the Microsoft Connector for Oracle. This new connector is interesting because we are no longer required to install an Oracle client to make it work. It also allows us to connect directly to an Oracle server without having to use a TNSNAMES.ORA file if we so choose, but the TNSNames file can still be used if desired.

There are separate downloads of the Microsoft Connector for Oracle for SQL Server 2019 and 2022. And you have to download the 32-bit driver and 64-bit driver separately.

Different Drivers for Different Connection Types

Attunity wasn’t the only way to connect to Oracle using SSIS. As noted above, you could also use a .Net provider for Oracle or an OLE DB provider for Oracle. To reduce complexity and increase supportability, I would advise you that you probably want to consolidate to use a single driver to connect to Oracle, unless you have a good reason not to.

This gets a bit confusing due to naming, so I’m linking to the current version of drivers (as of May 2023) to be used with SSIS target version 2022. These are the drivers I’m aware of that I’m sure are supported by either Microsoft or Oracle. There are some other drivers out there that are either no longer maintained or provided by third parties.

Connection TypeDriverProvider Name in Visual Studio
Oracle ConnectionMicrosoft Connector for OracleOracle Connection Manager
OLE DB ConnectionODAC 21cOracle Provider for OLE DB
ADO.Net ConnectionOracle Client for Microsoft ToolsOracleClient Data Provider

You’ll need to install both the 32-bit and 64-bit drivers. The 32-bit is used in Visual Studio when developing SSIS packages. The 64-bit driver is used by default when executing packages on the SSIS server (unless you configure the package to execute in 32-bit mode).

In SSIS 2022, if you are reviewing SSIS logs and see errors related to an Oracle connection, and you see mention of ODBC and the Oracle Connection Manager, that is the Microsoft Connector for Oracle.

If you see errors in the SSIS logs related to an Oracle connection, and you see mention of OLE DB provider OraOLEDB.Oracle.1, that is the driver from ODAC.

I hope this makes clear your options and required drivers for connecting SSIS to Oracle.


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?