Sunday 23 February 2014

SSIS vs Oracle - Something bad happened but no one's telling you what

Trying to run an SSIS 2012 package that accesses an Oracle database through ODP.net.  It works when running the package through Visual Studio, but fails once deployed with the error:

The type initializer for 'Oracle.DataAccess.Client.OracleConnection' threw an exception.


Ok, something bad happened. There may be an internal exception here, but I guess SSIS isn't going to tell us what it is.

This is complicated by the fact that every installer that Oracle has ever built is pretty much rubbish.

Did it install correctly?
Can't the installer tell me some sort of 'success' or 'failure' rather than just closing?
Is that an information message or an error message?
Where did all the files go?
What do I currently have installed?
Why is there no entry in the install/uninstall programs control panel?
Why is it getting so confused over x86 and x64?
What files is it actually trying to use?
Why are we still using .bat files for an install it's not 1996 anymore?
Why can't I uninstall this, the uninstaller does nothing!?

Apparently the error is sometimes related to x64 trying to access x86 and vice-versa. I had some type of ODP.net installed from months ago, but wasn't sure what version or whether it was x64 or x86. Couldn't figure out how to find that info out. I tried installing both x64 and x86 versions of ODP.net but due to the installers was unable to tell if they installed correctly and still had the above error anyway.

What did work was when executing the deployed package from the SSIS server you can tell it to use the 32-bit runtime:


So I dunno. I guess this means I have the x86 version of ODP.net installed. This is A Good Thing To Know.