How to connect to Oracle db from SSIS/SQL Server Data Tools?

sql-server oracle ssis odbc oracleclient
Sarah picture Sarah · Nov 29, 2012 · Viewed 16.2k times · Source

I have Oracle 11g r2 client installed on my 64 bit machine running Windows Server 2008 r2. Have the following installed: SQL Server 64 bit MS Office 64 bit

I tried connecting from excel/powerpivot to the oracle db, it works perfectly fine.

However, when I try to connect from SSIS to the Oracle db, I get a mismatch between the driver and application.

(TITLE: Connection Manager

Test connection failed because of an error in initializing provider. ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application )

How do I overcome this issue? Thanks!

Answer

Justin picture Justin · Nov 29, 2012

You need configure Oracle ODBC Driver 32 bit on:

c:\windows\sysWOW64\odbcad32.exe

SSIS of server 2008 r2 is always running on 32 bit no matter what SSIS say:)

  1. You need to edit runtime of SSIS package and set it to false to find 32 bit ODBC driver (first window) http://sqlblog.com/blogs/john_paul_cook/archive/2010/03/24/running-32-bit-ssis-in-a-64-bit-environment.aspx

  2. In "Agent" Job Step properties "Execution options" tab you need check "Use 32bit runtime" option.

Related questions

SSIS - the value cannot be converted because of a potential loss of data

I am relatively new to SSIS. I am trying to extract information from an Oracle database using Microsoft OLEDB for Oracle and I am using this query: SELECT ID FROM Test I get an error message saying: the value cannot …

Read More
Oracle OLE DB Provider not Listed in SSIS

I'm having an issue with SSIS and VS2015 CM. I have a package that needs to connect with Oracle to get some data. I installed the ODAC for Win64 and the Oracle client but I can't see the Oracle provider …

Read More
SQL Agent Job - Connection may not be configured correctly or you may not have the right permissions on this connection?

I'm getting this error when running an SSIS package through SQL Agent Failed to acquire connection "ORACLE ADO.NET". Connection may not be configured correctly or you may not have the right permissions on this connection. When I log on …

Read More