SSIS and Oracle providers - another warning

As you may have gathered I'm working with Oracle and SSIS a lot at the moment - and here's another gotcha with it:

Colleagues finalising a release encountered this error when testing SSIS packages on a 64-bit version of Windows 2003 R2:

ORA-06413: Connection not open

This is discussed at length in this MSDN thread amongst others.

The upshot is that the Oracle provider can't cope with parentheses (ie brackets) in the filepath of programs connecting to Oracle using the provider. Yes - you read that right.

Those of you who know anything about Windows running on X64 architecture know that 32-bit applications are stored under a seperate programs folder ie:

c:\Program Files (x86)\

The sorts of programs that live under this folder are Business Intelligence Development Studio (ie BIDS) which is 32-bit only, and the 32-bit version of DTExec.exe - which executes SSIS packages. So you can see the problem; any SSIS package that uses the Oracle provider and runs in a 32-bit context will error.

Fine you might say, don't run it under a 32-bit context then. Unfortunately if you're having to extract data from Excel (as we are) you have to - there is no 64-bit provider for Excel. Happily there is a fix and a workaround. The bug fix is patch number 5059238 available from Oracles MetaLink site. However you need a 'Support Identifier' which we haven't got hold of yet. I'll edit this post if we (or when) we try this fix.

The work-around is simply to avoid using parentheses when calling executables. The way this is done is by using the old-school MS-DOS short-name for the file path. Hence

c:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe

becomes

C:\Progra~2\Microsoft SQL Server\90\DTS\Binn\DTExec.exe

This avoids having a parenthesis in the file-path and therefore the Oracle provider won't error. In fact you can launch BIDS/Visual Studio from the command-line in a similar way - so you can check the connection managers work:

C:\Progra~2\Microsoft Visual Studio 8\Common7\IDE\devenv.exe

So to top this post off here is an example of a command-line that will run an SSIS package using the Oracle provider under a 32-bit context on a 64-bit server:

"C:\Progra~2\Microsoft SQL Server\90\DTS\Binn\DTExec.exe” /DTS "\MSDB\[Package Name]" /SERVER [Server Name]

More on Oracle connectors

A useful wiki here containing a plethora of info on SSIS connectivitity. There is a table here listing the different Oracle Providers. This also includes the points I made in my previous post. Since it appears under my post on a Google search for 'Oracle providers' I thought I'd try and redress the balance a little...

Oracle SSIS providers - careful!

Been pretty busy over the last couple of months (hence the hiatus in posting), but I've built up plenty of useful material to post about in the next few weeks (time permitting!).

Whats in a name? Well quite a lot when it comes to loading data into Oracle using SSIS. The details are described here but it boils down to using "Oracle Provider for OLE DB" rather than "Microsoft OLE DB Provider for Oracle".
The difference? Well as the article states:

[Microsoft OLE DB Provider for Oracle] has not been enhanced since Oracle 8

Therefore it doesn't support certain data types and 64-bit for starters.

Bear in mind however you need to install the Oracle client to get the Oracle Provider for OLE DB which is here.