Home > SSIS Best Practices > SSIS – Connecting to 64-bit Oracle

SSIS – Connecting to 64-bit Oracle

Problem

There is no problem with the linked server to the Oracle database on the 32-bit or 64-bit SQL Server. However, on the 64-bit server, SSIS connection to the 64-bit Oracle database failed.

Notice that the Log on to the server section is grayed out.

image

Here is the “oraoledb.oracle.1 is not registered” error from SSIS.

image

Potential Issues

1) Mismatch between 32-bit and 64-bit. I doubt this one. Because the SQL Server is 64-bit on this machine.

2) The port is not open on the firewall. What is the port number for SSIS to connect to an Oracle database?

3) Truly missing the driver. 

Analysis

Since the SSIS connection to the Oracle on the 32-bit SQL Server is working, I’ll start investigation from there.

Here are all the Providers installed on the 32-bit SQL Server.

MSDAORA: Microsoft OLEDB provider for Oracle

MSDASQL: Microsoft ODBC provider

OraOLEDB: Oracle Native OLEDB provider for Oracle

SQLOLEDB: Microsoft Native OLEDB provider

image

On the 64-bit SQL Server, we are missing MSDAORA. I don’t think this is the problem. Since we are not going to use the Microsoft OLEDB provider for Oracle anyway.

image

On the 32-bit server, using either Microsoft OLEDB provider for Oracle or Oracle Native OLEDB provider for Oracle worked fine in SSIS.

Using either Microsoft OLEDB provider for Oracle

image

Using Oracle Native OLEDB provider for Oracle

image

Solution

No solution so far. But since the linked server is working, I’ll use the linked server to extract raw data from the Oracle database.

Categories: SSIS Best Practices
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: