Job fails when run as SQL Agent

Hi,

I have developed a package which selects records from SQL 2000 table from a server on network and writes it into an excel sheet.

The package runs fine from IDE. I am using "DoNotSaveSensitive" option. I have deployed this package on my local server which is SQL 2005 with SP2.

and scheduled through a job to run as SQLAgent. But I am getting the following error:

Message

Executed as user: STEL540\SYSTEM. ...on 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:22:20 p.m.

Error: 2007-08-20 15:22:22.00

Code: 0xC0202009

Source: LastMonthCallTagging Connection manager "HAMDB02-GenCallTagging"

Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.

Error code: 0x80004005. An OLE DB record is available.

Source: "Microsoft SQL Native Client" Hresult: 0x80004005

Description: "Communication link failure". An OLE DB record is available.

Source: "Microsoft SQL Native Client" Hresult: 0x80004005

Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available.

Source: "Microsoft SQL Native Client" Hresult: 0x80004005

Description: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.".

End Error Error: 2007-08-20 15:22:22.02 Code: 0xC020... The package execution fa... The step failed.

Can someone please guide on this error?

Thanks,

How are you connecting to HAMDB02-GenCallTagging? Windows or sql server authentication?

|||

When you are running packages it is all about context, when you run it manually it runs in the context of your permissions, as a Job it runs in the context of SQL Server Agent. Try the links below for details the Agent and the owner of the package needs admin level permissions.

http://support.microsoft.com/kb/918760/

http://support.microsoft.com/kb/938086/en-US