asked on

Job Fails on SQL Agent but Runs Fine in MSDB

I need some assistance please. I have an SSIS package that was created in VS 2005. It runs fine in the IDE, it runs fine in MSDB. It fails as soon as I try and run it as a scheduled package. I have changed the login account for the agent to the domain admin account so rights should not be an issue. The agent run package is able to perform the SQL portion of it, but fails right when it tried to acquire the connection to the Visual Foxpro data provider. The error message in the history of the job is:

Executed as user: OCEANBEAUTY\obsidb. ...9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:25:53 PM Error: 2010-04-21 12:25:55.60 Code: 0xC0202009 Source: Package Connection manager "Ops NetYield" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2010-04-21 12:25:55.60 Code: 0xC020801C Source: Data Flow Task OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Ops NetYield" 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: 2010-04-21 12:25:55.60 C... The package execution fa... The step failed.

My development server can hit this same provider with no trouble. I compared all the settings and everything looks the same. What am I missing?

Reza Rad

could you try to use DTEXEC command in your sql server job to run the package ,
and let me know if you can run it in this way or not?

Reza Rad

here you can find how to use DTEXEC command:
http://msdn.microsoft.com/en-us/library/ms162810.aspx

vsddba

ASKER

It appears to have run successfully that way. That's similar to when I run it manually by executing the package in MSDB. It seems to just be when run as a job that the issue occurs which would point to security. The problem there is the agent is using a domain admin account. What are your thoughts?

Reza Rad

try the dtexec in a sql server job, and let me know the result.
you can select job step type as transact sql or cmdExec and use DTEXEC command line there.
I want to find out that could you run the dtexec on sql server job successfully or not?

vsddba

ASKER

Nope, same error.

Executed as user: OCEANBEAUTY\obsidb. ...9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:22:47 AM Progress: 2010-04-22 03:22:52.14 Source: Data Flow Task Validating: 0% complete End Progress Error: 2010-04-22 03:22:59.99 Code: 0xC0202009 Source: Package Connection manager "Ops NetYield" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2010-04-22 03:23:00.00 Code: 0xC020801C Source: Data Flow Task OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Ops NetYield" failed with error code 0xC0202009. There may be error messages posted before this with more information ... Process Exit Code 1. The step failed.

ASKER CERTIFIED SOLUTION

vsddba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Reza Rad

Glad to hear,
you solve it at last.
Regards,