DTS Scheduled Task Fails - Permission Denied

I searched the forum's threads on this, and while there were many results, none have helped so far.

I am running a DTS package that is an ACtiveX Script Task using VBScript. The script uses CreateObject() to create a FileSystemObject to copy an .MDB before importing the tables into SQL Server. I want to copy it because of Access' notoriety of corrupting, and this much data being pumped out of Access could force me to Compact & Repair. I would rather do that on a copy.

Function Main()

Dim FSO
Set FSO=CreateObject("Scripting.FileSystemObject")

FSO.CopyFile "\\<server>\<Share>\Data.mdb", "\\<server>\C$\DataCopy\Data.mdb"

Main = DTSTaskExecResult_Success
End Function

The DTS Package runs when I execute it from Ent. Manager, of course. It fails if scheduled, or course :(

I have set the Owner of the Scheduled Task to my domain account, which is also in the Adminstrators Group on the physical server with the SQL Server installation (Windows 2003 Server). I also did the unnecessary task of adding my domain account specifically to the destination folder, which is also Shared.

My sqlagent.exe service runs as SYSTEM on the server, so the SQLAgent should have no problem copying a file from one folder on the server to another.

The Scheduled Task fails with the common error:

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied
Error on Line 12
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied
Error on Line 12
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.

I checked this MS KB Article (http://support.microsoft.com/kb/q298725/), but the instructions after opening DCOMcnfg.exe do not follow what is shown in the WMI window on Windows 2003 Server :rolleyes: (i.e. there is no "Default Security" tab to click.)

I am at a loss here. Thanks for your help.I'll bump this once.|||I'm still working on it so not sure if what I suspect is the issue... but have a dts package that download a file, based on SQL statement from as/400 to sql table... runs great when I run it myself within the enterprise manager but when I try to call from a stored procedure via my exe it fails to run.

What I've deduced is that when I run it within the enterprise manager that it is, in essence, running on the client... locally and using my dll's, etc... however, when I run via stored procedure through my exe it is trying to run on the server, herein lies the problem (really strong guess at this point)... the server is not set up to connect to the as/400 and that's where it bombs.

I'm guessing that your sql server is having issues talking to your access database in much the same way that mine is having issues talking to the as/400. I've got the client software installed now and will be testing my theory shortly. Hope this helps you with your issue.

b|||Is the access db on the same server as Sql server?

If so, do non unc paths work? Such as :

FSO.CopyFile "c:\somefolder\Data.mdb", "c\DataCopy\Data.mdb"|||.. I have a package as well.
The thing is that if I put in a msgbox to execute directly before the ".deletefile", the package will run, but if I outcomment It it fails...
I can on the other hand do an ".copyfile" instead...
I only have the problem with the ".deletefile"
I would also appriciate all help i can get on this.

// selander

Function Main()

Dim Eso, E, E1, Ec
Set Eso = CreateObject("Scripting.FileSystemObject")
Set E = Eso.GetFolder("\\CLUST\SYS\IMPORT\ENT\")
Set Ec = E.Files
For Each E1 in Ec
if ucase(E1.name) = ucase(DTSGlobalVariables("strFileName").Value)+".ZIP" then
msgbox "FUBAR"
Eso.DeleteFile "\\CLUST\SYS\IMPORT\ENT\" + E1.name , True
exit for
end if
Next
Main = DTSTaskExecResult_Success
End Functionsqlsql