Friday, November 2, 2007

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.

10 comments:

Bekke said...

I feel your pain. I've had to do the same thing to get SSIS to work with Oracle via SQL 2005 64-bit.

Have you tried to create a report via Reporting Services (64-bit) with an Oracle data source? Since I have only the 32-bit Oracle client loaded on the server, SSRS won't run the report in 32-bit mode. I'm afraid to try loading the 64-bit Oracle client. Do you have any ideas or thoughts on this? Thanks!

Arpan said...

Ted,
I am trying to debug a SSIS package in BIDS which was developed in a 32 Bit environment. The machine on which I am trying to bebug is a 64Bit with Windows 2003. Our DBA has installed both the 64 Bit as well as the 32 Bit client on the 64bit machine.

When I open the package in BIDS, I am able to see the list of providers. I can create a data source using .NET Providers for Oledb\Oracle Provider for OLE DB and when I click the TEST button, the test succeeds. However, when I try to bebug the package by hitting F5, it errors out with the following error.

[bill_to_dim [83]] Error: System.InvalidOperationException: The 'OraOLEDB.Oracle.1' provider is not registered on the local machine. at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

I have checked to ensure that the oraOLEDB10.dll is registered on the machine. the Oracle\Home folder has full rights

Initially the package unable to list the providers in design time, but after we followed the steps outlined in your blog we got past that issue. Thanks for the detailed information.

What I dont understand is that if the connection succeeds in Design time why does it fail at Run Time.

Any help will be really appreciated.

Thanks!
AB

Ted Malone said...

bekke: I have not worked with Oracle sources and SSRS, so I can't be of any help there for you. Sorry about that. One thing that you might be able to do is use the Oracle Vista client. This seems to be a little tighter in 64-bit environments, but I'd test the heck out of it first.

Ted Malone said...

arpan: This problem occurs because even though the SSIS development environment is 32-bit, the execution environment by default is 64-bit... This is a tricky issue to solve, but one thing you can do is remove the 64-bit "DTS" path from your PATH environment variable and put the 32-bit path in it's place. This is a crazy workaround, but it does work.

Arpan said...

Thanks for the tip. I will give it a try and see if that works out.

Anonymous said...

Here's a bit of a different approach that may help some people:

http://www.artisconsulting.com/Blogs/tabid/94/EntryID/5/Default.aspx

Arpan said...
This comment has been removed by the author.
decisys said...

Hi Ted, I applied your suggestions to my 64 bit W2k3installation but without complete success. I am able to connect to an Oracle 64 bit machine for a data source now, but am not able to process from VS nor my SSIS package.

The SSIS package is a basic update package using the "Analysis Services Processing Task" and I have disabled the 64 bit runtime in SSIS. The execution constantly fails when executing from from SSMS with the same error: ...execution in 64 bit mode using Oracle 32 bit client...

Erm, the SISS package works and has been validated. There is plenty of info on offer webwise but apart from information overload there also appears to be disparate solutions on offer which have little or nothing to do with using the System.Data.OracleClient. Have you any ideas to offer?

Regards
D

Jorg Klein said...

I think the problems are history when you install the 32 and 64 bit Oracle 11G driver:

http://sqlblogcasts.com/blogs/jorg/archive/2009/10/15/connect-to-oracle-using-ssis-on-a-64-bits-os.aspx

Ryan & Teena said...

I've been searching the Oracle site, google'ing bing etc.. trying to find the patches (# 5383042, etc.. ) does anyone have a link?