Getting SSIS Packages To Run On A 64 Bit Box

We had some serious issues getting the SSIS packages to run on a 64 Bit platform which connects to Oracle database.

Mark Hill managed to get this bloddy thing to work. He wrote a simple document explaining what he did. I thought I would share it with all of you.

-----------------------------------------------------------------------------------
When we first approached this, I would have thought it should have been as easy as installing the 64 bit drivers from the Oracle CD and then copying a package across and running it.

This approach did not work, no matter which driver was selected at design time (Oracle Client, Dot Net Provider etc) we could not establish a connection.

When opening up the package on the server with the BIDS environment we found that we were getting bad image messages or init failures for the driver from the design dialog.

Going back to basics, I thought this through a little.

If BIDS is a 32 bit process (which it is), it cannot load the 64 bit drivers, therefore that would go someway to explaining the bad image error message.

Solution?! Let’s try and install the 32 bit Oracle client on the 64 bit box.

Install worked, however the 32bit drivers also failed to load under BIDS.

By this time I was pulling my hair out for real.

Next we developed a package on the 32bit laptop, was sure to choose the Oracle driver, copy it across and run it. No, the 64 bit run time of SSIS cannot run this package.

Back to the drawing board (web search)

Three pieces of interesting information (there is actually four)

1) There are two versions of SSIS installed on a 64 bit by default, one in x86 programs and the other in programs
2) The 32 bit Oracle drivers do not like x86 in their path name (may explain the odd 32 bit driver errors under BIDS)
3) A package can be told which subsystem to use, package properties, 32 bit or 64 bit.
4) If you use scripting code, ensure that the flag is set in the properties to allow dynamic recompile, or else you will get an error message loading the vbscript code on 64 bit.

Reality

Well there are two versions of SSIS, which you can run from the command line prompt, dtsexec.exe, if you use the /f option then you will be able to specify a filename for it to run.

The dynamic code flag, sometimes does not work, if you have this problem, open the package on the 64bit box and then resave it.

Eventually after all of this we were able to copy across a package and get it to work accessing an Oracle box.

All in all a bloody nightmare.

-------------------------------------------------------------------------------------

Thanks Mark

Sutha