SQL Server Agent Jobs fails but I can execute package manually

Question

SQL Server Agent Jobs fails but I can execute package manually

BKW

Ten Centuries

Points: 1350
More actions
May 11, 2005 at 3:26 pm

#102521

I have a package that downloads an excel spreadsheet to a table. I can execute the package manually and it runs OK. I scheduled a job to run at night.  I needed to test so when I started  the job, I get the following:
(Error string: 'X:\MYDIR\MYFILE.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.&nbsp
If the path was incorrect I could not execute this manually.  Any suggestions?
Changinagain

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Where you executing it manually from Enterprise Manager on your PC or from the Server? And, is the file path on your PC or the Server?

Greg

BKW Ten Centuries Points: 1350 More actions · Answer 2

Manually from Enterprise Manager on my PC. The file path is on the network server.

Changinagain

Rafeeque M.K. Ten Centuries Points: 1205 More actions · Answer 3

If you can execute manualy does'nt means that u can execute it by scheduling a job.While scheduling a job in sql server agent you should have to consider other things also.As you are stated you are acessing resource across the network i think this may causing the problem while running the scheduled job.Just check your sql server agent start up account that it is 'This Account'.If not change to this Account and enter a username and password of the domain account where sql server agent runs.

Note that This account enables you to specify in which windows NT/2000 Domain account sql server agent runs.Also the domain account you specify must be a member of the sysadmin role on the local instance of your sql server

Rafi-

PurpleLady SSCertifiable Points: 7819 More actions · Answer 4

Our experience with similar problems have generally been one of two things: resources not available to the server (for example, exporting an Excel spreadsheet would work manually but not as a scheduled job; exporting to a mapped drive) or permissions (for example, the SQL Agent account did not have the proper permissions to write to the folder or share).

BKW Ten Centuries Points: 1350 More actions · Answer 5

Thanks guys, I had to changed my package from the mapped drive to the UNC path, once I did that, the job executed fine.

Changinagain

Greg Charles SSC-Forever Points: 45403 More actions · Answer 6

So, to summarize, when you run a package manually, it runs on your PC under your security context and when you schedule it, it runs on the server under the SQL Server Agent account's security context. Your problem can be avoided by always using UNC paths and ensuring that the SQL Server Agent account has access to the location.

Greg