Oracle connection issue - 32/64 bit issue

Question

I was having problems connecting to Oracle in a SSIS package that I  scheduled in SQL Server 2012 Agent. This is a 64 bit server and oracle was also 64 bit. We then installed 32 bit oracle and then in BIDS selected the option "Run64bitRuntime" as false and it started working fine while running package from BIDS. But the scheduled job is not working. As suggested in some forums I enabled "Use 32 bit runtime" in the execution option, but still its giving error as below

Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.2100.60 for 32-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  8:43:15 AM  Error: 2014-03-25 08:43:16.55     Code: 0xC0202009     Source: SyncViadat2ESMS Connection manager "DBINGRAMT.INGRAM.DE.esmst1"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x8007007E.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x8007007E  Description: "The specified module could not be found.".  End Error  Error: 2014-03-25 08:43:16.56     Code: 0xC020801C     Source: Transfer scan rules to temp OLE DB Source [37]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "DBINGRAMT.INGRAM.DE.esmst1" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2014-03-25 08:43:16.56     Code: 0xC0047017     Source: Transfer scan rules to temp SSIS.Pipeline     Description: OLE DB Source failed validation and returned error code 0xC020801C.  End Error  Error: 2014-03-25 08:43:16.58     Code: 0xC004700C     Source: Transfer scan rules to temp SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2014-03-25 08:43:16.59     Code: 0xC0024107     Source: Transfer scan rules to temp      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  8:43:15 AM  Finished: 8:43:16 AM  Elapsed:  1.435 seconds.  The package execution failed.  The step failed.

Please help...

Answer 1

Hello,

When scheduling also, you will have an option to change the run-time environment to 32-bit.

try that, hope it helps.

Thanks,

Anilkumar

Answer 2

I've already done that. Enabled "Use 32 bit runtime" in the execution option. That didn't help.

Thanks,

Kavitha

Answer 3

Where are you scheduling the job? In same server or remote server? Does that server also have required Oracle provider installed? Is the server 64 bit or 32 bit?

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 4

Job is scheduled on the same server where its working on BIDS. Server is 64 bit. Changed the BIDS/job to run on 32 bit and its working fine on BIDS but not on schedule. We have SQL server 2012 and its scheduled as a SQL server job.

Answer 5

Ok..Then only other thing that needs to check is whether you've the correct provider installed for Oracle in machine.

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 6

Thanks for the reply!!!

Initially on this 64 bit server, we had 64 bit oracle installed. On opening the SSIS project in BIDS, it complained that provider not found. When I open data connection "Oracle provider for OLEDB" was not getting listed. As suggested by some post I tried registering ORAOLEDB11.dll manually, but that too didn't help. So our Oracle DBA installed 32-bit oracle and then made that as home and after that this provider is listed. After changing the parameter "Run64bitRuntime" to "false", it started working fine on BIDS. So I think we do have the right provider installed.(unless what we did is wrong).

Answer 7

To load the Oracle client the correct oci.dll must be in the path.  Check the system path and reboot.  Services like SQL Agent don't see system path changes until a reboot.

David

---

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

Answer 8

Thanks for the reply!!!

Initially on this 64 bit server, we had 64 bit oracle installed. On opening the SSIS project in BIDS, it complained that provider not found. When I open data connection "Oracle provider for OLEDB" was not getting listed. As suggested by some post I tried registering ORAOLEDB11.dll manually, but that too didn't help. So our Oracle DBA installed 32-bit oracle and then made that as home and after that this provider is listed. After changing the parameter "Run64bitRuntime" to "false", it started working fine on BIDS. So I think we do have the right provider installed.(unless what we did is wrong).

Did your admin uninstall the 64-bit driver when installing the 32-bit? We have both 32-bit and 64-bit installed, our connection to Oracle works fine via both BIDS and SQLServer (without setting the "Run 32-bit" option)

Answer 9

Thanks a lot. Its working after reboot. Had to wait till Sunday for the reboot as UAT is in progress.

Kavitha

Answer 10

Thanks