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

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

Job Failes due to Error = -2147467259 (80004005)

Hello,

I am importing an excel spreadsheet into SQL Server 2000. The dts package runs perfectly when you execute it under the Local Package pane.

My problem is when i schedule a job to execute the DTS package giving me the same error. I can only successfully execute the package under local Packages. Under Jobs i receive this error:

Executed as user: H-OB\Administrator. ...rt: Copy Data from 'Xagent - Report$' to [CIMPRO1].[dbo].[Xagent_Star_Coater_Points] Step DTSRun OnError: Copy Data from 'Xagent - Report$' to [CIMPRO1].[dbo].[Xagent_Star_Coater_Points] Step, Error = -2147467259 (80004005) Error string: 'R:\shared\star coater\Monthly Points Rpt\Current_Month_Points.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error source: Microsoft JET Database Engine Help file: Help context: 5003044 Error Detail Records: Error: -2147467259 (80004005); Provider Error: -534774783 (E01FFC01) Error string: 'R:\shared\star coater\Monthly Points Rpt\Current_Month_Points.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error source: Microsoft JET Database Engine ... Process Exit Code 1. The step failed.

Any help on this error would be more than appreciated.The dts package runs perfectly when you execute it under the Local Package pane.
With this I assume you mean that it works when you execute it from your workstation which is not the SQL Server Machine.

At that moment the package is execute on your own computer (not at the server), under your own credentials.

When the job executes it's running under SQL Server credentials at the server and it seems it has never heard of an R-drive at that end or has no access to the mentioned folders or files. Make sure the account under which the SQL Server service is running can access that file.|||Thanks for the advice. I looked at the accounts that is used for the mssqlserver service and that was fine.

It turns ou that for some reason the server is not pickin gup the mapped drice. I had to type the full path in so that it could recognize the file that i am retreiving form the other server.

Thanks again.