Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65

    Unanswered: 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.
    Code:
    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:
    Quote Originally Posted by Evil SQL Server
    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, but the instructions after opening DCOMcnfg.exe do not follow what is shown in the WMI window on Windows 2003 Server (i.e. there is no "Default Security" tab to click.)

    I am at a loss here. Thanks for your help.

  2. #2
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    I'll bump this once.
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  3. #3
    Join Date
    Jan 2005
    Location
    Central Oregon
    Posts
    5

    sounds similar to my issue...

    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

  4. #4
    Join Date
    Dec 2004
    Location
    CA, USA
    Posts
    63
    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"

  5. #5
    Join Date
    Apr 2003
    Posts
    4

    Question same problem..

    .. 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)+".Z IP" then
    msgbox "FUBAR"
    Eso.DeleteFile "\\CLUST\SYS\IMPORT\ENT\" + E1.name , True
    exit for
    end if
    Next
    Main = DTSTaskExecResult_Success
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •