Package fails when run under SQL Server Agent

Hi,

I have a SSIS package that I developed using Business Intelligence Development Studio. I imported it into our SQL Server database and it runs fine (when I connect to Integration Services within SQL Server Management Studio, and run the package). But when I created a SQL Server Agent job comprising 1 step, to run this package, it fails. The failing step attempts to read data from a MS Access 2000 DB into SQL Server 2005.

I added logging to the package, and got the following error logged to the Event Viewer:
"The AcquireConnection method call to the connection manager "CurrentYearPCStats" failed with error code 0xC0202009."

I then created a new Operating System (CmdExec) step, calling DTEXEC with the command line copied from that generated by the SSIS step. The command line is:
"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEXEC.EXE" /SQL "\AFAReduction\AFA Reduction Integration" /SERVER <ourservername> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

This failed with the same error in the Event Viewer, but for some reason also produced a meaningful error in the SQL Server Agent job's View History:
Description: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine cannot open the file '\\<anotherservername>\PCStatslive\CurrData.mdb'. It is already opened exclusively by another user, or you need permission to view its data."

Please note that the SQL Server Agent runs using user id sqlservice, by default, and I have amended this user's Windows account to make it a Domain Admin.

Please let me know why I might get this error, when the package runs OK on its own.

Many thanks,
Keith.

The jobs running with the sql agent usually run with the agent's service account identity (local system or Network service).

This user must have permissions to open your Access DB files (NTFS security etc included).

When you run it from SQL mgmt studio it is running using your own credentials...

Hope this helps.

|||In addition to Zoran's advice, take a look at this KB article which describes common problems with running in Agent, and how to troubleshoot them:
http://support.microsoft.com/kb/918760|||

i have a similar kind of situation As a inidividual package or executing through the command line the package runs fine but when i try to execute it through the Agent it fails.

Executed as user: SEMASTER\Administrator. The package execution failed. The step failed.

Any help

Thanks,

Jasmine

|||

Hi again,

Thanks for your replies. You could've knocked me down with a feather - I came in this morning and the SQL Agent job that runs at 6 AM had succeeded! Tried manually and its all working fine! I've changed nothing, I have the Job History to prove that the last test I did yesterday failed, and everything since 6 AM today has succeeded.

The only thing I can guess is that there was some problem with the Access database itself, which cleared overnight. I'll keep an eye on this.

Thanks again,

Keith.