sql server - Oracle provider for Oledb missing in VS 2015 Shell

Question

I am migrating to SSIS 2016 version. I am trying to use the Oracle provider for Oledb in connections.

However, this option does not show. I have installed the Oracle client 12.2 and I am able to do UDL file testing, where in I can see the Oracle provider and able to test connection.

But when I try in VS 2015, the option is not shown.

The issue is described here - https://jorgklein.com/2011/06/02/ssis-connect-to-oracle-on-a-64-bit-machine-updated-for-ssis-2008-r2/ and based on this I have installed the 64 bit client . But when trying to use the connection manager, the oracle provider is not shown.

How to fix this?

Answer 1

The recommended way to connect to Oracle from SSIS is using Attunity Connectors with guarantee high performance and are easy to use. If you choose to work with Oracle Provider for OLEDB than you should download Oracle Data Access Components (ODAC) and the Oracle Visual Studio Tools.

In this answer i will provide detailed information about how to connect to Oracle from SSIS and i will provide some links to the solution for your issue

---

There are many way to connect to oracle from SSIS:

(1) Oracle Provider for OLEDB

Which is a part of the Oracle Data Access Components (ODAC)

• Oracle - Visual Studio Tools
• 32-bit Oracle Data Access Components (ODAC) and NuGet Downloads
• Oracle Provider for OLEDB

The following link contains a similar issue:

• Oracle OLE DB Provider not Listed in SSIS

(2) Microsoft OLEDB Provider for Oracle

Which is a part from Microsoft Data Access Components (MDAC)

This feature will be removed in a future version of Windows. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, use Oracle's OLE DB provider.

• Microsoft OLE DB Provider for Oracle Overview

(3) Oracle Attunity connectors

Attunity's high speed connectors for Oracle and Teradata have been selected by Microsoft to be included with SQL Server Integration Services (SSIS).

• Microsoft Connectors for Oracle and Teradata by Attunity for Integration Services (SSIS)
• Microsoft Connectors by Attunity

---

Discussion

• SSIS Connectors for Oracle by Attunity vs Oracle provider for OLE DB performances
• Oracle Client DataProvider VS Oracle Provider for OLEDB

---

Other helpful links

• Importing From Oracle In SSIS (from Oracle client installation to data import)
• Microsoft OLE DB Provider for Oracle (MSDAORA) for 64 bit?
• SSIS with Oracle Connectors