SQL Server Integration Services, Oracle Client and the 64-bit Platform

I was reminded today that I had promised some time ago to create a post about my experiences with SSIS, Oracle and 64-bit environments.

The current project that I am responsible for, Configuresoft's Configuration Intelligence Analytics, is built to extract data from a number of source systems, including those housed in an Oracle Database. One of our major Joint Development Partners has standardized on 64-bit operating systems for all tools, including our CIA product. As it turns out, many of our development environments are 64-bit as well, so from my perspective I figured this would be an easy scenario to support.

WRONG!

As it turns out, the Oracle client is very peculiar when installed into 64-bit environments. The installer will detect the fact that it is 64-bit native, and by default will only register 64-bit instances of itself. This is all well and good of course, *if* you are using 64-bit native components all through the communication channel (i.e, if the application you're building is 64-bit). The unfortunate thing is that the SSIS development environment is a 32-bit application. So, this means that when you initially install a 64-bit Oracle client, you actually cannot use Business Intelligence Development Studio (BIDS) or Visual Studio in order to test (or even validate) the Oracle-facing SSIS components. Fortunately, Oracle has thought that through and provided the ability to install a 32-bit version of the client components. Again, all well and good, except for the fact that by default, when you use DTEXEC to execute the SSIS packages (or use a SQLAgent job), you're actually running a native 64-bit application. ARRRRRRRRRRRRRRGH! So, you test and validate the package in BIDS and it succeeds, but fails when you run for "production" purposes.

To make matters worse, even if you properly install and register the Oracle client (I'm specifically talking about the Oracle 10 client) you'll still have problems due to the fact that there is a bug in the 10.2.x.x Oracle client that precludes the use of many special characters, either in a username or program path (WTF?!?!) that uses the client. The bug is Oracle bug # 3807408 and it is fixed in Oracle patch # 5383042. But wait, there's more!! The path is valid only for the 10.2.0.2 Oracle client, which CANNOT be downloaded anywhere. You must patch a 10.2.0.1 client to get the 10.2.0.2 version. This patch is available (oh by the way, it's only available for 32-bit environments!) as Oracle patch # 4547817.

Oh, and did I mention that you have to pay Oracle for the patches?

Did I say ARRRRRRRRRRRRRRRRRRRRRRRRRRRRRGH! yet?

By now I'm sure (assuming you've read this far) that you're wishing I'd just cut to the chase...

 

Oracle and SSIS 64-bit Happiness

The following steps are in my opinion the only steps you can take to get SSIS and Oracle to play well in 64-bit environments:

1. Install the Oracle 10.2.0.1 32-bit client (This is the client that is available from Oracle's website)
2. Install the Oracle # 4547817 patch (which will upgrade your client to 10.2.0.2)
3. Install the Oracle # 5383042 patch (fixes Oracle bug # 3807408)
4. Develop your SSIS Packages
5. When ready to execute the package, do NOT use a SQLAgent SSIS Package task to execute them. Use a CMDExec task that explicitly uses the 32-bit version of DTEXEC (located in Program Files (x86)\Microsoft SQL Server\90\DTS\binn)

The above steps will work 100% of the time. However, you will lose the 64-bit execution environment benefits (better memory management basically) in your SSIS packages. You may want to consider breaking out any package that requires Oracle Connectivity to run as a separate job, that way you can limit the impact of the 32-bit only environment.