Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2008
    Posts
    12

    Unanswered: DTS Scheduling problem

    Hi,

    I have a DTS (SQL 2000) which copies a new excel file and dumps data executed from a SP into it. I have used the Activex script task for copying an excel file.
    It works fine when executed manually but when scheduled it fails. It deletes the XL file but doesn't copy the file. I think the problem occurs from the line

    Set e_app = CreateObject("Excel.Application")

    I don't know how to resolve it. Plz help

    The code is as below:
    Function Main()

    Dim sSourceFile
    Dim sDestinationFile

    Dim oFSO
    Dim e_app
    Dim e_wbook

    Main = DTSTaskExecResult_Success
    Set oFSO = CreateObject("Scripting.FileSystemObject")

    sSourceFile = "\\AAA\Fold1\FileCopy\TransportDiscount.xls"
    sDestinationFile = "\\\AAA\Fold1\ARTA\TransportDiscount.xls"

    if oFSO.FileExists(sSourceFile) then
    If oFSO.FileExists(sDestinationFile) Then
    oFSO.DeleteFile sDestinationFile
    end if

    Set e_app = CreateObject("Excel.Application")
    Set e_wbook = e_app.Workbooks.Open(sSourceFile)

    With e_wbook
    .SaveAs sDestinationFile
    .save
    End With

    e_wbook.Close
    Set e_wbook = Nothing
    e_app.Quit
    Set e_app = Nothing
    else
    Main = DTSTaskExecResult_Failure
    end if
    Set oFSO = nothing

    Main = DTSTaskExecResult_Success
    End Function

  2. #2
    Join Date
    Mar 2008
    Location
    Tacoma, WA
    Posts
    11
    Does the username assigned to the SQL Agent service have permissions to the \\AAA\Fold1 sub folders which contain the source and dest file(s)?

    Any more details as to the error message itself?

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    sDestinationFile = "\\\AAA\Fold1\ARTA\TransportDiscount.xls"

  4. #4
    Join Date
    Mar 2008
    Location
    Tacoma, WA
    Posts
    11
    That could be the problem as well.

  5. #5
    Join Date
    Mar 2008
    Posts
    12
    sDestinationFile = "\\\AAA\Fold1\ARTA\TransportDiscount.xls"

    The back slash is just a typing error. Sorry about that.
    But the DTS when sheduled to work till deleting the file "\\AAA\Fold1\ARTA\TransportDiscount.xls" works fine.

    It gives error on addition of the line
    Set e_app = CreateObject("Excel.Application")

    Any idea why this happens?

    The error msg when viewing the job history is:--
    The job failed. The Job was invoked by Schedule 575 (transportDiscountFTSStuds). The last step to run was step 1 (transportDiscountFTSStuds).

    Step1 is the Activex script task.


    Thanks
    Payal

  6. #6
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    xtremenw is on the right track. When you run the package manually, you're running it as yourself. When you schedule it, it runs under the SQLAgent user account. Check to see if the SQLAgent account can access both \\AAA\Fold1\ARTA and \\AAA\Fold1\FileCopy.

  7. #7
    Join Date
    Mar 2008
    Posts
    12
    I will chk this out and 'll get back.

    Thank You

  8. #8
    Join Date
    Mar 2008
    Posts
    12
    Hi,

    Can you please tell me how to check for whether the SQLAgent account can access some folders or not.

    Thanks & regards,
    Payal

  9. #9
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    In the services control panel, does the SQL Server Agent service log on as a user account, or as 'local system'? If it's a user account, make sure that user has permissions to access that folder. If it's 'Local System', then NTAuthority\System would have to have access to that folder, and then, only if it's on the local machine. (I'm assuming it isn't since you're using UNC paths for your files.)

    If you need help simply checking permissions on a folder, I suggest you talk with your system or Windows administrator.

    -D.

  10. #10
    Join Date
    Mar 2008
    Posts
    12
    Thank You

    Payal

Posting Permissions

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