SQL Server Performance Forum – Threads Archive

Scheduled DTS Fails

I have 4 scheduled DTS jobs that have been running without error for 3 years. They truncate SQL tables and import a text file into the tables. These run nightly. All of a sudden they have been failing. I know that generally when these fail there is a access/rights issue. Nothing changed that should affect this that I am aware of – a security patch was applied to the server (I am working on finding out exactly what patch). I can right click the DTS and execute, no problem. I can open indesign mode and execute, no problem. But if I right click the job and start, it fails almost immediately. I tried deleting the jobs and recreating them – no good. I have not made any headway whatsoever with the error:
==
Executed as user: HGINT06SYSTEM. DTSRun: Loading… Error: -2147024629 (8007010B); Provider Error: 0 (0) Error string: The directory name is invalid. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713. Process Exit Code 1. The step failed.
== The path is valid and available, it has not changed. There is a 5th job, that does essentially the same thing as these 4, and it runs nightly without error. Its owner is the same as the other 4, the difference is that this job is a script and not a DTS job. ==This runs on:
Microsoft SQL Server 2000 – 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
== Has anyone run into anything like this? Thank you, thank-you all in advance for any help or insight! Karen "There are two documents in your in-box: tragedy and comedy. Your Pick." –Dale Dauten

Security patches that were applied are as follows: KB834707, KB824151, KB840987, KB841356, KB841533, KB883935 Karen "There are two documents in your in-box: tragedy and comedy. Your Pick." –Dale Dauten

Check the privileges for the SQLAgent service account on the involved directories. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

The service runs under the local system account. What do I look for? …I’m not sure that is the place to look since there is a job that runs accessing the same path as the 4 that fail. All jobs run under a sysadmin account. Thanks! Karen "There are two documents in your in-box: tragedy and comedy. Your Pick." –Dale Dauten

I changed the service account to an NT sysadmin account. The jobs run now, but I’ll have to watch to see if all my other jobs run overnight. I can only assume that one of the patches limited the access of local system account that the service ran under. Thanks! Karen "There are two documents in your in-box: tragedy and comedy. Your Pick." –Dale Dauten

I do not believe that this is the long term solution and only a quick fix, as there is a 5th similar job that did not fail. The only difference between the 4 that fail and the 1 that does not is that the 1 that works is a script, not a dts job. Karen "There are two documents in your in-box: tragedy and comedy. Your Pick." –Dale Dauten

Being the SQL service is set under local system account the DTS job which is scheduled via SQLAgent is unable to perform set of actions due to restrictions and no privilege for local system account. MS always suggest to use a local NT account with similar name & password if the DTS is involved in accessing/maintaining file across the network for ease of portability. Usually, a package run from DTS Designer, the DTS Import/Export Wizard, the DTS Run utility, or from the command prompt executes under the security context of the user who is currently logged in. However, a package scheduled for execution runs under the security context of the SQL Server Agent job that runs the package. The owner of that job may or may not be the same as the user currently logged in. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks, Satya. That was a nice concise way to state that. I originally had my services under an NT account, but had problems such as this running them. The only way I could get all jobs to run reliably, dts or otherwise, was to have the service log in as local system account. The job have run fine for the last 3 years, but now, all of a sudden, these 4 started to fail. The other 20 nightly jobs had no problem. This morning it looks like everything ran fine last night, with the service set to a sysadmin account. Let me ask you this. What rights do you give an account that will only exist to run the agent jobs? I thought I read that you should not run the service with full sysadmin rights. Maybe I’ll play with this, if I can find the time. <img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> The company I work for is closing & so there is little reason to try to improve things, and there are tons & tons of things to do in preparation for the close. Thanks again! Karen "There are two documents in your in-box: tragedy and comedy. Your Pick." –Dale Dauten

What kind of job (select or insert) is that is required to perform DTS actions on that database? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

The jobs in question truncate a table and then insert data from a text file. Thanks! Karen "There are two documents in your in-box: tragedy and comedy. Your Pick." –Dale Dauten

You’re right this Account doesn’t require full SYSADMIN rights on that box.
Then ensure to grant privilege for that SQLAgent account to access that text file on the server’s directory and correct rights on the database to truncate a table and insert data, i.e., DB_DATAWRITER fixed db role. Article fromhttp://www.databasejournal.com/features/mssql/article.php/3349561 for your reference. HTH
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>