CODEWITHSUNDEEP
sqlsql-serveroracle-databaseauthenticationssis
Gus
Gus

Reputation: 91

SQL Server Agent 2012 not executing SSIS 2012 package that connects to Oracle but runs OK from SSIS

I've been fighting this for a while now, and tried many things, I am finally overwhelmed.

I had many SSIS packages in SQL Server 2008 working perfectly, bringing data from SQL, MySQL and Oracle. Then I decided to upgrade to SQL 2012 and the packages won't run. I created them all again using VS2010 and they run great from SSIS, but the ones that connect to Oracle do not work. After several errors, I got this:

Executed as user: Domain\UserName. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 12:00:00 PM Error: 2014-03-01 12:00:04.25 Code: 0xC0208449 Source: SalesBacklog ADO NET Source [2] Description: ADO NET Source has failed to acquire the connection {01F532A5-4542-4833-9103-5FCAEC4EA4A2} with the following error message: "Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.". End Error Error: 2014-03-01 12:00:04.25 Code: 0xC0047017 Source: SalesBacklog SSIS.Pipeline Description: ADO NET Source failed validation and returned error code 0xC0208449. End Error Error: 2014-03-01 12:00:04.25 Code: 0xC004700C Source: SalesBacklog SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2014-03-01 12:00:04.25 Code: 0xC0024107 Source: SalesBacklog Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:00:00 PM Finished: 12:00:04 PM Elapsed: 3.297 seconds. The package execution failed. The step failed.

My new server specs are:

I've installed, uninstalled, over and over, in different order, the Oracle 32 and 64 Clients. But no luck.

Lately I strongly believe that my problem might be related to permissions of the SQL Agent to run the package and not a problem with the Oracle Clients. I tried all types of authentications (which are quite confusing at times) and I get another wide arrange of errors.

Is anyone out there with any idea of how should I go about it? I am starting from scratch and I will try every step, all over again. If I find the issue, I'll keep you posted.

Thank you!

Upvotes: 1

Views: 7085

Answers (1)

Gus
Gus

Reputation: 91

After much effort, @billinkc told me all I needed to know: check box for 32bit mode

  1. Open the SQL Agent Job
  2. On the "Steps" section, select the step that executes the SSIS Package that connects to Oracle
  3. Under the "Execution options" tab, check the check box "Use 32bit runtime"

This time though I made sure to deinstall all Oracle Clients and just install the 32bit Oracle Client.

Upvotes: 1

Related Questions