DTS Package Fails When Scheduled

[cocopud]

I have a simple DTS package that takes data from SQL Server 2000 using a sql select statement and copies it to a .csv file on a mapped drive to another ftp server. I can open the job and it runs fine, but when I try to schedule it, it keeps failing. I am getting the following errors below. It says it can't find the path specified, but it can find it when it is run and not scheduled

Executed as user: WDB03\SYSTEM. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: Copy Data from Results to H:\cr.csv Step DTSRun OnError: Copy Data from Results to H:\cr.csv Step, Error = -2147467259 (80004005) Error string: Error opening datafile: The system cannot find the path specified. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 3 (3); Provider Error: 3 (3) Error string: Error opening datafile: The system cannot find the path specified. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: Copy Data from Results to H:\cr.csv Step DTSRun: Package execution complete. Process Exit Code 1. The step failed.

 

[unclerico]

Is the FTP server part of your domain?? You need to execute the job with a login other than WDB03\SYSTEM preferably a domain account.

 

[Eyespi20]

You need to change your path from H:\cr.csv to \\servername\cr.csv

While you can see mapped drives (that's what that letter is), the server can't,and that's why your job is failing.

Margaret

 

[cocopud]

How do I execute the package as another account. When I create the DTS package I use the export data wizard. On the first screen it asks for a data source user/password. I changed this to the SQL server sa account since the other option is Windows Authentication, but no option to enter in the account. This is also the same for the last screen Save DTS Package. Again when I initially create the package it works, but if I go to jobs and run it fails with the same error, so I'm not sure how to run it under a different account.

Also, when I mapped the network drive I used the server name; however, when I chose a destination for the text file it automatically puts it in with the H: and I tryed to change it manually but I also get the same error if I try to execute the scheduled job.

 

[unclerico]

When you manually execute jobs, they execute in the context of the logged on user; however, when you schedule jobs they execute in the context of the account running the services. Change the account that runs both the MSSQLSERVER and SQLSERVERAGENT services to a domain account; currently you have them executing under Local System. Make sure that this new account has the proper permissions in place on the target computer (the H:\ drive).