none
SSIS Package Oracle ODBC connection fails after driver upgrade

    Question

  • I have a 64bit Windows SQL Server and a 64bit Windows Oracle server. The SQL Server has both the 64bit and 32bit drivers installed. Everything worked fine until I upgraded the 64bit drivers from 10 to 11. Now when I run the SSIS package from BIDS it completes fine. When it's run from SQL Server Agent it fails wit the error

    Error: ODBC Source failed validatoin and returned error code 0x80004005.

    I recall having this issue many jobs before and it had something to do with the driver install sequence. I tried upgrading the 32bit drivers from 10 to 11 but that didn't help. Any ideas?

    Monday, September 16, 2013 2:10 PM

Answers

  • So I setup a proxy account for the account I was using with BIDS and changed the SQL job to run the steps as that account, and it worked. So how does this relate back to the drivers? Why does one account work and the other doesn't?
    Monday, September 16, 2013 3:10 PM

All replies

  • Hi,

    why both 32bit and 64bit drivers installed; are the oracle home properly set

    which driver was upgraded; what is the oracle version you running

    SQL agent and BIDS running under different users and different context ?

    Monday, September 16, 2013 2:24 PM
  • So the difference is in accounts only. So what happens if you run the package using your account on the server manually (no Agent involved)?

    Arthur My Blog

    Monday, September 16, 2013 2:25 PM
  • @Sarfraz.Ali BIDS needs the 32bit driver, SQL Server needs the 64bit driver.  SSIS can use either, depending on whether you run the package in 32bit or 64bit mode.

    @ArthurZ +1 on checking permissions. see PRB: Error Message: 0x80004005: General Error Unable to Open Registry Key.  But that error code is generic: E_FAIL.  So it might be something else too.

    Another common issue in this configuration is that the OleDB or ODBC driver needs to load the Oracle Call Interface library, OCI.DLL.  And OCI.DLL is a Win32 DLL, and is loaded by the Oracle drivers using a regular LoadLibrary() call.  So it's finds OCI.DLL using Windows Dynamic Link Library Search Order.  Here it means that the PATH environment variable will be used to locate OCI.DLL.  If the 32bit ODBC or OleDB driver finds the 64bit OCI.DLL (or vice-versa) it will attempt to load it and fail. 

    So it may be necessary to configure the system path correctly for 64bit programs, and use a batch file to manipulate the PATH before launching any 32bit programs (like BIDS).

    David


    David http://blogs.msdn.com/b/dbrowne/



    Monday, September 16, 2013 2:36 PM
  • So I setup a proxy account for the account I was using with BIDS and changed the SQL job to run the steps as that account, and it worked. So how does this relate back to the drivers? Why does one account work and the other doesn't?
    Monday, September 16, 2013 3:10 PM
  • PackageProtectionlevel may be at fault, or simply the account running the package in the SSIS job step of the Agent has no rights over the new driver directories or something else in the PATH.

    I even don't know why needed to upgrade.


    Arthur My Blog

    Monday, September 16, 2013 3:16 PM
  • did you set job to run as 32 bit in job setup 
    Monday, September 16, 2013 7:53 PM
  • Yes, it was always set as 32bit both in BIDS and in the SQL job.
    Monday, September 16, 2013 7:57 PM
  • To exactly which version of Oracle driver - 11.1 or 11.2?

    What I learned (hard way) that Oracle changed Unicode handling in 11.2:

    For the same Oracle database, same client, different driver version on client:

    10.2 and 11.1 reports VARCHAR2 columns as non-unicode with code page, e.g. 1525 which is encoded unicode

    11.2 reports the same columns as native Unicode, so package fails with the error that it cannot convert unicode column to non-unicode.

    If it is the case - 11.1 in BIDS, 11.2 on the server - use exactly the same version of the driver.

    In my case I postponed upgrade to 11.2 and we will have to rewrite some of the packages during the upgrade...


    Tuesday, September 17, 2013 2:31 AM