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?
vsddbaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Reza RadConsultant, TrainerCommented:
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?
0
Reza RadConsultant, TrainerCommented:
here you can find how to use DTEXEC command:
http://msdn.microsoft.com/en-us/library/ms162810.aspx
0
vsddbaAuthor Commented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Reza RadConsultant, TrainerCommented:
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?
0
vsddbaAuthor Commented:
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.
0
vsddbaAuthor Commented:
PROBLEM SOLVED!

The Visual FoxPro OLEDB Driver evidently has an option to install for all users or just the current user. When it was installed, it was evidently installed for the current user and NOT all users which explains the class not registered error. I uninstalled and then reinstalled the driver and it's working great now. Thanks for everyone's help!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Reza RadConsultant, TrainerCommented:
Glad to hear,
you solve it at last.
Regards,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.