Can anyone help with some SQL Server 2000 DTS/job issues?

Saint Nick

Lifer
Jan 21, 2005
17,722
6
81
I scheduled one of my DTS packages that is generating a flat file. Runs great when I execute it manually, but for some reason, SQL Server Agent can't run my job. And supposedly it has access to the share I am writing to.

Here is the error I got:

Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Error opening datafile: Access is denied.
Step Error code: 80004005
Step Error Help File:DTSFFile.hlp
Step Error Help Context ID:0

What gives?
 

Nothinman

Elite Member
Sep 14, 2001
30,672
0
0
I scheduled one of my DTS packages that is generating a flat file. Runs great when I execute it manually, but for some reason, SQL Server Agent can't run my job. And supposedly it has access to the share I am writing to.

Here is the error I got:



What gives?

You say you're writing to a share, so are you doing it via UNC? Have you tried a local path to make sure that works?
 

Monoman

Platinum Member
Mar 4, 2001
2,163
0
76
How about check the user who is running the SQL Server Agent process, bet it's either localservice or some other local user. Check to see if that user has permission to the share.
 

Nothinman

Elite Member
Sep 14, 2001
30,672
0
0
How about check the user who is running the SQL Server Agent process, bet it's either localservice or some other local user. Check to see if that user has permission to the share.

Yea, that was going to be my next suggestion.