SQL Server Performance Forum – Threads Archive
Error executing DTS through Job
Hi,<br /><br />I am very new to all this so please bare with me! I started this job thinking it was nice and simple … silly me!<br /><br />I want to run a sql query that exports the data to a .txt file on a share on another server. Both servers are Win2k and runnig SQL 7.0.<br /><br />I have set up the DTS package and that works fine until I schedule it as a job, then it fails with the following …<br /><br />DTSRun: Loading… DTSRun: Executing… DTSRun OnStart: Copy Data from Results to \tchx-wb1URL_LIST_CHANGEurls.txt Step DTSRun OnError: Copy Data from Results to \tchx-wb1URL_LIST_CHANGEurls.txt Step, Error = -2147217887 (80040E21) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 1326 (52E); Provider Error: 1326 (52E) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: Copy Data from Results to \tchx-wb1URL_LIST_CHANGEurls.txt Step DTSRun: Package execution complete. Process Exit Code 1. The step failed.<br /><br />Any help greatfully appreciated, I’m very lost here! (Please remember I am new to this so ‘simplistic’ responses please <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br />Many thanks.<br /><br />Regards,<br /><br />Dave
Ensure the SQLAgent service account has required privileges to carry on the task in the DTS package.
Follow thru this KBA http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074] which gives you the idea and troubleshoot the issues in scheduling DTS as a job. 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.
forgive me but, is that basically saying that the sqlserver agent has to be started using an NT account that has write permissions to the share i’m trying to write to? Thanks. Regards, Dave
Yes it is, all domain user accounts must have permission to:
Access and change the SQL Server directory or any other directory that involves file sharing by a task.
Access and change the .mdf, .ndf, and .ldf database files.
Log on as a service.
Read and write registry keys. If the startup account assigned to the MSSQLServer Service is not a member of the Local Administrators group, or if the BUILTINAdministrators SQL Server login has been removed, you must add the startup account for the MSSQLServer service or the SQLServerAgent service, or both, to the SQL Server system administrators (sysadmin) role. Grant the [DomainNTaccount] user a logon to SQL Server. 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.
Thank you Satya, your help is much appreciated. I will endevour to complete this and let you know how I get on. Thanks again. Regards, Dave
If you’re unsure about the above mentioned list of privileges then take help of network adminstrator on the basis of network. 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.
Also have a look at this topic: "Calling a DTS package from within a SP"
Farhad If the underlying user doesn’t have permission to access the table on remote computer then the package itself will fail irrespective of running from a SP or schedule the job. 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.
Thanks to you all … I got there in the end [<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />]<br /><br />Regards,<br /><br />Dave
Hi
Iam running a job on SQL Server 2005 which executes an SSIS Package which sources data from SQL Server 2000 and deletes few records. The SQL Agent account for both these instances are different and would remain different based on the decision made. I have given the SQL Agent account that is used to execute this job on SQL Server 2005 required dml privilege to delete the records in SQL Server 2000. but my package fails with Process Exit Code 1. The step failed..
Please help. I tried to look up to the link provided above but that’s not available.
]]>