DTS job fails when Scheduled?

I have a DTS job that moves a text file from a workstation to the SQL server, transforms the data, pumps into a table and then archives the file. It runs fine within EM on the SQL Server, but once scheduled fails immediately on Step 1. I have logged the error as follows:

************************************************** **********
The execution of the following DTS Package succeeded:

Package Name: Load LaserGauge Data Daily
Package Description: (null)
Package ID: {6E4E8DDC-3864-43E9-B82D-300FCBCCAE63}
Package Version: {4DFF3F73-1653-4896-8DA3-A4246D41EE51}
Package Execution Lineage: {E17CD8A1-9754-413B-B129-093711326C27}
Executed On: SEQUEL
Executed By: SQLSERVICE
Execution Started: 3/26/2004 1:12:17 PM
Execution Completed: 3/26/2004 1:12:17 PM
Total Execution Time: 0.031 seconds

Package Steps execution information:

Step 'DTSStep_DTSDataPumpTask_1' was not executed

Step 'DTSStep_DTSActiveScriptTask_1' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution.
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:700

Step Execution Started: 3/26/2004 1:12:17 PM
Step Execution Completed: 3/26/2004 1:12:17 PM
Total Step Execution Time: 0.031 seconds
Progress count in Step: 0

Step 'DTSStep_DTSActiveScriptTask_3' was not executed
************************************************** **********

I have taken the following actions to resolve:
1. Tested DTS job on local SQL Server - executes without error
2. Checked security: The DTS job is owned by me, and I am a member sysadmins, etc... The SQL Job I have tried several differing accounts (Mine, Domain Administrator, and most recently a proxy account I setup called SQLDTSuser) All have failed with the same error.
3. I have checked the SQL Agent account to ensure that it has necessary rights, stopped and started SQL Agent service, same results.
4. As mentioned I setup a Proxy Account which is in Domain Admins, and Sysadmins on SQL and changed SQL Agent Properties to use this account. result was same error.
5. Set Workflow Properties on the DTS job ActiveXtask to "Execute on Main PAckage Thread" - result was same error.

I'm at a loss I have to be overlooking something obvious, but need someone to hopefully point me in the right direction. Thank you in advance for any help I can get!

The platform is SQL 2000 and I've included the first activex Task code below:

************************************************** **********
' Visual Basic ActiveX Script
'************************************************* **********

Function Main()
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Dim filePath
filePath = DTSGlobalVariables("gv_SourceDir").Value & "\" & SourceFileName
If fso.FileExists(filePath) Then
fso.MoveFile filePath, DTSGlobalVariables("gv_WorkDir").Value & "\" & DTSGlobalVariables("gv_WorkFileName").Value
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End Function

Function SourceFileName
Dim fileDate
fileDate = DateAdd("d", -1, Date)
SourceFileName = "LASERDATA" & PadLeft(DatePart("d", fileDate), 2) & PadLeft(DatePart("m", fileDate), 2) & Right(DatePart("yyyy", fileDate), 2) & ".CSV"
End Function

Function PadLeft(Value, Length)
Dim retval
retval = Value
Do While Len(retval) < Length
retval = "0" & retval
Loop
PadLeft = retval
End FunctionMy initial thought is the line

filePath = DTSGlobalVariables("gv_SourceDir").Value & "\" & SourceFileName

...

A scheduled task runs on the server not the workstation and it is unlikely that your server actually has the same path defined as your workstation.|||Thanks for the reply Simsoph, I thought I wasn't going to get any help there for awhile.

We are able to manually run the DTS job on the SQL Server console and it completes successfully. That tells me that the paths used, which are UNC and stored in a global variable for the DTS job, are working. I've followed all the TIDs mentioned for security and the like but this one has me stumped?

Thanks|||I'll give the credit to Simsoph as I have been able to resolve my problem. Simsoph mentioned the file paths, which although they weren't the problem the global variables were! For some reason when scheduling via the DTS menu the GV weren't getting saved as part of the job. I ran the GUI DTS util and set the GV's within the advanced section and scheduled from it instead and presto the scheduled job now runs. Very strange I hope this helps someone else in the future.

Thanks
Kevin...|||Its always better to use UNC file paths than specifying drive letters and names.|||have you noticed that UNC paths suffer some strange performance loss -- better to map a drive for the performance and add the mapping of the drive into the application.

--
I have been working on a VB6.0 project which uses the DTS objects to crate a package and add steps for a package for each file in a folder. There can be up to 1000 files in this folder. I was getting ;

The execution of the following DTS Package failed:

Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Package failed because Step 'X:\EventLog\Events_130404004649_130404010050_0000 .tsv' failed.
Error code: 80040428
\Error Help File:sqldts80.hlp
Error Help Context ID:700

Now I have added "Execute on Main PAckage Thread = true" to each step in the package and have had none of these errors since.. I have done 3 full runs of 2000 files now. So I am hoping that has fixored it.