Hey folks,

Why does my job fail when it is scheduled, but works perfectly when run
manually?

I've even changed the owner to 'dbo', and tried scheduling it as 'sa'?

But it still doesn't work?

Help.....

This is the errors?

Executed as user: ABZAPPS603\SYSTEM. ...art:
DTSStep_DTSExecuteSQLTask_2
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: Copy Data from BABids to [MRS].[DBO].[BABids] Step
DTSRun OnError: Copy Data from BABids to [MRS].[DBO].[BABids] Step,
Error = -2147467259 (80004005)
Error string: Error opening datafile: Access is denied.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp
Help context: 0
Error Detail Records:
Error: 5 (5); Provider Error: 5 (5)
Error string: Error opening datafile: Access is denied.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp Help context: 0
DTSRun OnFinish: Copy Data from BABids to [MRS].[DBO].[BABids] Step
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTa...
Process Exit Code 1. The step failed.

You have the error:
Error string: Error opening datafile: Access is denied.
so it's a permissions issue.
When you can run it from your PC manually, it means it
executes fine from your PC and under your security context.
When it runs as a job, it executes from the server and under
the security context of SQL Agent (if job is owned by a
sysadmin) or the proxy account (if job is owned by a
non-sysadmin). So you need to check the permissions for the
service account for SQL Agent to make sure it has access to
the file you are importing. If the file is located on a
network location, the service account needs to be a domain
account to access network resources.
Take a look at the following Knowledge Base article for more
info:
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/?id=269074

-Sue

On 8 Aug 2006 07:11:32 -0700, "StuartGMilton"
<stuart.milton (AT) pragma (DOT) co.uk> wrote:

My DBA pal just assigned my db user some new rights, and it works.

Cheers for your reply.