Phil's fairly useful blog: SSIS and Oracle providers

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: