SQL Server Performance Forum – Threads Archive

DTS Package Failure as a Scheduled Job

Hi:
I have been having a wierd problem with my DTS. It runs perfectly when I have it run manually but for some strange reason, it does not execute correctly when scheduled as a job…i.e. using the SQL Server Agent. I get the following error: Executed as user: sbcity_wanadministrator. …elete from Table [FMDSSTemp].[dbo].[APPOST03] Step DTSRun OnStart: Delete from Table [FMDSSTemp].[dbo].[APPOST04] Step DTSRun OnStart: Delete from Table [FMDSSTemp].[dbo].[APYCONTROL] Step DTSRun OnStart: Delete from Table [FMDSSTemp].[dbo].[ARPOST03] Step DTSRun OnFinish: Delete from Table [FMDSSTemp].[dbo].[APPOST04] Step DTSRun OnStart: Copy Data from APPOST04.CSV to [FMDSSTemp].[dbo].[APPOST04] Step DTSRun OnFinish: Delete from Table [FMDSSTemp].[dbo].[APPOST03] Step DTSRun OnFinish: Delete from Table [FMDSSTemp].[dbo].[APYCONTROL] Step DTSRun OnFinish: Delete from Table [FMDSSTemp].[dbo].[ARPOST03] Step DTSRun OnProgress: Copy Data from APPOST04.CSV to [FMDSSTemp].[dbo].[APPOST04] Step; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnProgress: Copy Data from APPOST04.CSV to [FMDSSTemp].[dbo].[APPOST04] Step; 2000 Rows have been transforme… Process Exit Code 1. The step failed. Please help or advise!!!!! Amit Shah
Web Developer
Civic Resource Group

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.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Hi :
I have followed the exact same steps as described in the article. I logged in as the administrator (network account admin) on the SQL Server and then mapped the network drive to the other server that holds all the text files that I extract into my SQL server. This would then probably represent a trusted connection. I also checked that the owner of the job is the administrator so all in all, it should have worked fine. But somehow, this is just not the case. Please let me know if there are any other ways to get around this problem
Thanks!!![B)] Amit Shah
Web Developer
Civic Resource Group

Enable the DTS package log and see the steps executed for the DTS.
BTW in what user context the MSSQLSErverAgent runs and its privileges? 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.

Hi Satya:
The steps seem to execute correctly for the DTS log. I also checked the MSSQLSERVERAGENT service and it runs as a Network account i.e. with the priveleges of the gloabl administrator. Please let me know what other specifics are required.
Thanks
Amit Shah
Web Developer
Civic Resource Group

Confirm the level of service pack on SQL & OS.
What is the significance of the DTS package? 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.

]]>