Connecting to Oracle on 64-bit (x64) machine

A teammate of mine might have found the problem and "solution" (it really is a messy workaround right now). Stupid parantheis :-)

Here are some excerpts from a support document

Applies to:

Oracle Net Services - Version: 8.1.7.0 to 10.2.0.1
Oracle Data Provider for .NET - Version: 8.1.7.0 to 10.2.0.1
Oracle Objects for OLE - Version: 8.1.7.0 to 10.2.0.1
Oracle Provider for OLE DB - Version: 8.1.7.0 to 10.2.0.1
Oracle ODBC Driver - Version: 8.1.7.0 to 10.2.0.1
Microsoft Windows XP (64-bit Itanium)
Microsoft Windows Server 2003 (64-bit Itanium)
Microsoft Windows XP (64-bit AMD64 and Intel EM64T)
Microsoft Windows Server 2003 (64-bit AMD64 and Intel EM64T)

 "C:\Program Files (x86)\..."

"C:\Program Files\..."

I tried this on a machine to start Visual Studio and it still gave the same error I actually created a shortcut on the desktop and then used it.

Temporarily, I execute the DOS format name from the Run command and it connects to Oracle. Do you guys know if the Oracle bug has been fixed I can get my client to patch it in. I assume the bug is in the Oracle 9.2 client.

Thanks

Can you elaborate on Solution 2 Did you just rename the root 32-bit path and change the PATH in Environment variables

Also, on install there is no way to configure the directory for 32 bit components Is there anyway to configure the destination 32bit tools directories on install   If not, we can put it on the wish list.

It may not be a problem in SSIS but in the driver. Arr you able to connect to the data source from another app running on the same box

The issue I ran into - if you have 32-bit driver - SSIS does not like it very much runningon 64-bit

it seems the oracle patch should apply on client and server.

patch 3807408, unfortunately, the patch is available only for the currently certified versions (9.2.0.7, 10.2.0.1) and not on our current version (9.2.0.4). On top of the database server patch need to apply another patch for the client machine (where DTS/SSIS is running from):

I ran into the same problem, the issue as was previously mentioned is because the client components (management studio) are installed into a folder with (x86) in the folder name. There is no control over this path name at install time because the SQL Server install routine is doing it. The majority of the management studio is compiled as a 32 bit application so that is why the (x86) is being appended to the folder where the studio is being installed. When you attampt to import data from within the management studio, the data import wizard (DTSWizard) is passing a string to the Oracle provider with (x86) in the string, this exposes a networking bug in Oracle and it breaks on the parenthesis.

There are a couple of workarounds to this and neither are very pretty or what you would want to do in production, but here is a simple workaround that will at least allow you to connect to Oracle and import some data. When you go through the SQL Server installation you have a choice of where to direct the installation of the database services, the data files, and the client components. You can either accept the defaults which go to the C: drive or you can choose another drive and custom folder name. Let's say for the client tools you chose an install path and folder name as follows:

D:\ClientTools

What you would end up with after the installation is complete is this:

D:\ClientTools
D:\ClientTools (x86)

Notice you now have two folders called ClientTools, the first one is where some 64 Bit compiled applications are, and the one with the (x86) are where the majority of the client tools are installed, specifically the management studio and the business intelligence development studio.

Workaround:

Copy, Paste, and Rename the entire ClientTools (x86) folder as follows:

D:\ClientToolsx86

Using explorer or from a command prompt, navgiate to the sub folder under ClientToolsx86 and find the DTSWizard, it should be in a folder similar to D:\ClientToolsx86\90\DTS\Binn

Execute the DTSWizard.exe and you should be able to successfully connect to Oracle and import data.

One of the many problems with this is although you can connect and import data, you can't set it up as an SSIS package or a job under the SQL Agent because there are a ton of registry keys that point to (x86) as the default location for the management studio

I have experimented with hacking the registry and changing all the paths and I did get it working. The last problem I ran into was although I could save and execute an SSIS package, it failed to run as a job under the SQL Agent with no explanation as to why.

Oracle does provide an x64 Client and the provider does connect, but it cannot import data because there is a data Precision issue where it cannot convert to Float, Numeric with the proper precision and scale. There is a bug submitted for a patch but it has not been released yet.

To connect to Oracle using the x64 Oracle client provider you need to execute the x64 DTSWizard which is not in the management studio, it is located in the D:\ClientTools\90\DTS\Binn folder, this is where the 64 Bit compiled applications are.

Crazy huh

That is a very good idea.

I use the old DOS 8.3 format name when executing DEVENV.exe. This will allow you to validate Oracle connections but not run the pkg.

To run use the DOS 8.3 path and DTEXEC.exe from 32 bit directory. I also store my connection configurtion in XML and use the command file to execute. The only thing you need to secure is the XML file.

Oracle has released a patch that resolves the (x86) issue on x64 Windows servers. I tested the patch on two servers and I can now connect to an Oracle server via the default locations for SQL management studio and the Business intelligence development studio. After creating my data import packages in SSIS I was able to set up a job using the SQL Agent, point to the SSIS packages and run them, and the jobs are now succeeding.

Caution, there is a bug somewhere but I'm not sure where, but when you are in the BI studio and you drag an OLE DB connection onto the page, make sure you set the "Always use default code page property" to "true" otherwise your packages will fail due to an authentication problem with the provider. I am unclear as to whether or not this is a SQL problem or an Oracle problem. Also if you are in the SQL management studio and you use the data import wizard (DTS) to import data from Oracle and save your data import as an SSIS package, the package will fail to run as a job due to the default code page property issue.

The process I am using that works is:

Use the BI studio to create your data import packages (SSIS), make sure you set the "Always use default code page property" to "true" for the Oracle OLE DB connection, and then finish creating the remainder of your package

Save your SSIS package to disk or (file system)

Use the SQL management studio and connect to Integration Services to import your SSIS package

Use the SQL management studio and connect to the database engine to set up a SQL Agent job that points to your SSIS package within Integration Services

Run your job and it should work.

The two patches you will need from Oracle are on Metalink so you will need an account to download them:

Both of these patches are for the Oracle 10.2.0.1 32 Bit Client

5059238 - Patch for (x86) issue

http://updates.oracle.com/ARULink/PatchDetails/process_form patch_num=5059238

4751549 - "opatch"

http://updates.oracle.com/ARULink/PatchDetails/process_form patch_num=4751549

Make sure you double check these patches to be sure they are the exact ones you need, I have a hard time following Oracle's numbering schemes.

I experieced the same issues and this is how i solved it.
(Without patches)

On Windows 2003 x64 with, SSIS(x64)

Install a x64 version of the oracle client.

Copy

C:\Program Files (x86)\Microsoft SQL Server
and
C:\Program Files(x86)\Microsoft Visual Studio 8

to a directory withouth parenthesis in the name i.e "C:\ProgramFilesx86"

Start the dev inviroment from the new location in Microsoft Visual Studio 8\Common7\IDE\devenv.exe

Change OleDb connections for .Net Oracle Provider connections and use a Datareader as apposed to a DataSource.