I'm having the same problem....
> It's working! Thanks for all your help along the way. For
> anyone who comes across this thread in the future, let me
> summarize what I've learned.
> 1) Set things up so that the DTS package and the Job both
> write to the Application event log, for both success and
> failure. The resulting detailed error messages are more
> helpful than what you'll see in the "Job History".
> 2) On the server where the DTS package runs, it may well
> need write access to certain folders, EVEN if the package
> itself does not involve any "write" steps. To run
> successfully, DTS will create some log files and some
> scripting files. In my case, this is what caused the
> failure.
> 3) To find out what folders will be written to (see 2
> above), the best I could do was grab the name of the file
> that could not be written (from event log message; see 1)
> and then do a search on the whole drive for such a file.
> Thanks Allan.
> >-----Original Message-----
> >Thank you again. It looks like the DTS package wants to
> >write to the
> >C:\Program Files\Microsoft SQL Server\80\Tools
> >directory, so I've added read/write/list permissions.
> >Will update this thread shortly if it works!
> >>-----Original Message-----
> >>Yes give the BLUEBOX\sqlworker account write perms to
> >the directory you want
> >>it to write to.
> >>--
> >>Allan Mitchell (Microsoft SQL Server MVP)
> >>MCSE,MCDBA
> >>www.SQLDTS.com
> >>I support PASS - the definitive, global community
> >>for SQL Server professionals - http://www.sqlpass.org
> >>"JB" <re...@to.newsgroup> wrote in message
> >>news:006801c304ac$9a422ce0$3401280a@phx.gbl...
> >>> Thank you again Allan.
> >>> I made "sa" the owner of the SQL Server Agent job, and
> >>> set up logging. The job still fails, but this time I
> >have
> >>> more detailed information.
> >>> From what I see, the error messages confirm your
> >>> suspicions, in that the failure is due to
> >a "CreateFile"
> >>> access denied error, when trying to write to a .LOG
> >file.
> >>> Below I am attaching the actual error message. It says
> >>> that the DTS package was...
> >>> Executed On: BLUEBOX
> >>> Executed By: sqlworker
> >>> "BLUEBOX\sqlworker" is the account under which SQL
> >Server
> >>> and SQL Server Agent run. Do you think that this
> >account
> >>> needs write access to the DATA directory?
> >>> Here are the details from the two errors written to
> >>> the "Application" event log. Note that the source
> >>> database (located on the BLUEBOX server, where the DTS
> >>> package resides in SQL Server) is called "PPS". The
> >>> destination database (on a remote server) is
> >>> named "PPS_1".
> >>> -------------- Source: SQLSERVERAGENT --
> >>> SQL Server Scheduled Job 'Backup production DB to
> >>> Intercraft (PPS_1)'
> >(0xFFC812264946F744942BBE3AFFB25BD9) -
> >>> Status: Failed - Invoked on: 2003-04-17 05:51:00 -
> >>> Message: The job failed. The Job was invoked by
> >Schedule
> >>> 2 (Backup production DB to Intercraft (PPS_1)). The
> >last
> >>> step to run was step 1 (Backup production DB to
> >>> Intercraft (PPS_1)).
> >>> -------------- Source: DataTransformationServices --
> >>> The execution of the following DTS Package succeeded:
> >>> Package Name: Backup production DB to Intercraft
> >(PPS_1)
> >>> Package Description: (null)
> >>> Package ID: {661464DF-3BD7-469D-AA97-19927FFFBEE9}
> >>> Package Version: {68C193A2-B913-4CC0-B5F7-
> F9A1B0BFA45C}
> >>> Package Execution Lineage: {BB66467B-8B0E-46E9-A10E-
> >>> 21FF593C17FF}
> >>> Executed On: BLUEBOX
> >>> Executed By: sqlworker
> >>> Execution Started: 17/04/2003 05:51:00
> >>> Execution Completed: 17/04/2003 05:51:01
> >>> Total Execution Time: 0.063 seconds
> >>> Package Steps execution information:
> >>> Step 'DTSStep_DTSTransferObjectsTask_1' failed
> >>> Step Error Source: Microsoft Data Transformation
> >Services
> >>> (DTS) Package
> >>> Step Error Description:Access is denied.
> >>> (Microsoft Data Transformation Services (DTS) Package
> >>> (80070005): Access is denied.
> >>> ) (Microsoft SQL-DMO (80004005): [SQL-DMO]CreateFile
> >>> error on 'BLUEBOX.PPS.LOG'. Access is denied.
> >>> )
> >>> Step Error code: 80070005
> >>> Step Error Help File:sqldts80.hlp
> >>> Step Error Help Context ID:1100
> >>> Step Execution Started: 17/04/2003 05:51:00
> >>> Step Execution Completed: 17/04/2003 05:51:01
> >>> Total Step Execution Time: 0.063 seconds
> >>> Progress count in Step: 0
> >>> ---------------
> >>> Thank you for your continued help,
> >>> JB
> >>> >-----Original Message-----
> >>> >SYSADMIN is God. There needs to be no permissions
> >>> explicitly granted on any
> >>> >databases as they are implictly granted them.
> >>> >Change the owner of the job to "sa".
> >>> >The SQLAgent service account needs permissions onto
> >any
> >>> filesystem locations
> >>> >local or remote. it also needs permissions on the
> >>> Source and destination
> >>> >SQL Server objects it manipulates.
> >>> >--
> >>> >Allan Mitchell (Microsoft SQL Server MVP)
> >>> >MCSE,MCDBA
> >>> >www.SQLDTS.com
> >>> >I support PASS - the definitive, global community
> >>> >for SQL Server professionals - http://www.sqlpass.org
> >>> >"JB" <re...@to.newsgroup> wrote in message
> >>> >news:064201c303fa$35e89c50$a101280a@phx.gbl...
> >>> >> Thank you for the quick response.
> >>> >> I have setup the job to log errors to a text file,
> >as
> >>> you
> >>> >> suggest.
> >>> >> Let me clarify the matter regarding KB article
> >269074:
> >>> I
> >>> >> do not know, or believe for that matter, that said
> >>> >> article does not apply to this case. After reading
> >it,
> >>> >> and other advice, I changed ownership of the "Job"
> >to
> >>> be
> >>> >> the same account (BLUEBOX\sqlworker) used to run
> the
> >>> SQL
> >>> >> Server Agent process (as detailed below). I hoped
> >this
> >>> >> would fix things, but it hasn't. Unfortunately, the
> >>> >> BLUEBOX\sqlworker account does not permit remote
> >login
> >>> >> for security reasons -- it is to be used solely for
> >>> >> running the SQL instance.
> >>> >> FYI, the BLUEBOX\sqlworker account IS a member of
> >the
> >>> >> SQL "sysadmin" server role (as required per KB
> >>> article).
> >>> >> Having rechecked the setup just now, I have
> noticed
> >the
> >>> >> following fact: the BLUEBOX\sqlworker account is a
> >>> member
> >>> >> of "sysadmin", but under that login's "Database
> >Access"
> >>> >> property, I see that it has NOT been given "permit"
> >>> >> priviliges for ANY of the databases (including the
> >one
> >>> >> used in this matter). Could this, even though the
> >login
> >>> >> is a "sysadmin", result in the error I've been
> >seeing?
> >>> >> Thank you again for your help -- I've tried to
> >ensure
> >>> >> that my setup follows the guidelines set out in
> the
> >KB
> >>> >> article, but am somewhat confused.
> >>> >> >-----Original Message-----
> >>> >> >The error
> >>> >> >-2147024891 (80070005)
> >>> >> >means
> >>> >> >General access denied error
> >>> >> >If you log into the box as the SQL Server Agent
> >>> account
> >>> >> and execute it
> >>> >> >interactively as you have just done logged in as
> >you
> >>> >> what happens. This
> >>> >> >will highlight the Agent being the problem. If
> you
> >>> have
> >>> >> made sure that the
> >>> >> >article
> >>> >> >http://support.microsoft.com/?kbid=269074 does not
> >>> apply
> >>> >> to you.
> >>> >> >In the package enable logging to a text file.
> >>> >> >--
> >>> >> >Allan Mitchell (Microsoft SQL Server MVP)
> >>> >> >MCSE,MCDBA
> >>> >> >www.SQLDTS.com
> >>> >> >I support PASS - the definitive, global community
> >>> >> >for SQL Server professionals -
> >http://www.sqlpass.org
> >>> >> >"JB" <re...@to.newsgroup> wrote in message
> >>> >> >news:011d01c303f2$2a189950$2f01280a@phx.gbl...
> >>> >> >> I have created a simple DTS package to copy a
> >>> database
> >>> >> >> from our production server to a development box
> >on a
> >>> >> >> nightly basis.
> >>> >> >> The production server's name is:
> >>> >> >> BLUEBOX
> >>> >> >> I created the DTS package (and scheduled it)
> >while
> >>> >> logged
> >>> >> >> into the server using Terminal Services. The
> >Windows
> >>> >> >> account I log on as is:
> >>> >> >> BLUEBOX\jmb-sg
> >>> >> >> The DTS package has just one task: "Copy SQL
> >Server
> >>> >> >> Objects". The settings for this task are:
> >>> >> >> Source database: (local), using Windows
> >>> >> >> Authentication
> >>> >> >> Destination database: xx.xxx.xx.xx (I.P. of
> >>> >> >> dev box), using an SQL account on that box
> >>> >> >> The DTS package properties are:
> >>> >> >> Owner/Creator: BLUEBOX\jmb-sg
> >>> >> >> Computer: BLUEBOX
> >>> >> >> The "sqlservr.exe" process runs under a
> dedicated
> >>> >> Windows
> >>> >> >> account: BLUEBOX\sqlworker
> >>> >> >> The "sqlagent.exe" process runs under the same
> >>> Windows
> >>> >> >> account: BLUEBOX\sqlworker
> >>> >> >> The SQL Server Agent job (scheduled to run the
> >above
> >>> >> DTS
> >>> >> >> package once per night) has the owner:
> >>> >> >> Owner: BLUEBOX\sqlworker
> >>> >> >> Currently, the job has failed each night,
> >producting
> >>> >> the
> >>> >> >> following detailed error message:
> >>> >> >> Executed as user: BLUEBOX\sqlworker. ...ing...
> >>> DTSRun
> >>> >> >> OnStart: DTSStep_DTSTransferObjectsTask_1
> >DTSRun
...
by