Post reply

DTS Package failing as a Scheduled Job

  • September 17, 2009 at 12:05 am

    #138202

    Hi folks,

    for fortnighly refresh i copy 2 zip files from an FTP site to a Remote destop.

    from these Zip files i extraxt+save two .txt files into theri respective sublfolders in this remote desktop.

    F:\UploadFolder\SUbfolder1\File1.txt

    F:\UploadFolder\SUbfolder2\File2.txt

    After that i schedule an Upload job in SQL 2005 to run overnight, this job basically calls a DTS Upload package in the remote desktop and uses the above saved files via connection managers named File1.txt and File2.txt.

    Now my problem started about a month ago when the sysytem adminstrator as part of his serurity audit took away access rights to the UploadFolder in the remoter desktop.

    Since then the SQL Scheduled job has failed each time with the same error message:-

    Message

    Executed as user: XXXX\idstartupusersql. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:50:00 PM Error: 2009-09-16 21:50:01.84 Code: 0xC001401E Source: UploadFile1 Connection manager "File1.txt" Description: The file name " F:\UploadFolder\SUbfolder1\File1.txt" specified in the connection was not valid. End Error Error: 2009-09-16 21:50:01.84 Code: 0xC001401D Source: UploadFile1 Description: Connection "File1.txt" failed validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:50:00 PM Finished: 9:50:02 PM Elapsed: 1.203 seconds. The package execution failed. The step failed.

    I get this for both files, even though all rights/permissions to all the folders in the remote desktop have been restored.

    BUt when I manually run the Uploadfile1 or UPloadFile2 DTS package in remote desktop it all works perfectly , however each time i try run DTS from the SQL 2205 scheduled Job it brings about a failure, which I suspect was due to Job owner: XXXX\idstartupusersql account not having the required permissions/rights etc. BUt even though those permission are restored the error message remains the samne.

    Connection managers for bothe files in DTS are OK since runnig them manually works fine.

    LET ME KNOW WHAT YOU THINK may be causing all this?

  • September 17, 2009 at 10:01 am

    #1055101

    Duplicate post. Direct replies here: http://www.sqlservercentral.com/Forums/Topic789390-364-1.aspx

    Greg

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply