4 Replies Latest reply on Aug 30, 2010 2:38 AM by 794510

    Connection issues between 64 bit SSIS and Oracle in 64bit

    445547
      I've got a Win2K3 Std Ed server (x64) running 64-bit SQL Server 2005 Enterprise Edition. I've installed the Oracle 10g 64 bit and and installed ODAC 64 bit then oracle patch to take care of () issue, i managed to create a link server on management studio and connect to oracle a 64 bit server

      Now... when I try to create a new connection manager in SQL Server 2005 Integration Services i got the following error, the OLEDB provider for Oracle can't be found, and when I try to manually add an underlying OLEDB connection to the database, SQL Server reports: i got the following error....
      Test connection failed because of an error in initializing provider. The 'OraOLEDB.Oracle.1' provider is not registered on the local machine.

      Note: my sql server agent is not running on this server, let me know if this is an issue
        • 1. Re: Connection issues between 64 bit SSIS and Oracle in 64bit
          599315
          We are running into a very similiar issue. We have a 2005 SQL Server system in which the link server runs fine with the Oracle Provider for OLE DB, but the Legacy DTS's fail with the same type of error, i.e., "error initializing provider". We believe it's related to a 32bit / 64 bit issue where the 64bit MS software is not able to see the Oracle provider. Have you determined the exact cause and found a fix yet?
          • 2. Re: Connection issues between 64 bit SSIS and Oracle in 64bit
            445547
            what we did was to install both oracle clients one 64 and 32 on the server with sql server 2005 and now i can use link server as well as creating packages and datasources on SSIS
            • 3. Re: Connection issues between 64 bit SSIS and Oracle in 64bit
              733672
              mtaji wa maskini,

              Cheers for the day.

              We came across your post of successful installlation of Oracle 32 and 64 bit versions ,for connecting Oracle from BIDS 2008.

              We are facing similar problem in connecting.We have a server with operating system Windows 2008 Server Standard Edition and SQL Server 2008 (x64 bit) installed on it.

              Now we want to connect to a Oracle database,for that we installed Oracle 11 g client full (32 bit and 64 bit) versions.Also changed the settings in the tnsnames.ora file by giving the host name of Oracle server.But there is still porblem in connecting to the oracle database server.

              "oraoledb.oracle.1 provider is not registered"

              Can we please guide us through the process of installation.We highly appreciate your time and efforts.


              Bhavesh
              • 4. Re: Connection issues between 64 bit SSIS and Oracle in 64bit
                794510
                Hopes this helps others, I spent almost the whole day trying to setup Win 2008 64bit server using SQL 2008 and SSIS. In addition to installing the Oracle 11g 64bit and configuring the environment variables and the tnsnames.ora, I still had access problems within SSIS connecting to Oracle tables. Looking over another working server which was 32bit, I found that I installed ODAC with ODT for Visual Studio on it some time ago, probably to fix this problem.

                At the Oracle page: http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html
                Download: Oracle 11g Release 2 ODAC and Oracle Developer Tools for Visual Studio 11.2.0.1.2

                Then install it on the server. You can test it by starting SSIS on the server, that is if it is not production, to test successful connection. It instantly resolved the SSIS problem for me.

                Good Luck!