Post reply

Restarting DTS on failure

  • March 18, 2003 at 9:54 am

    #80487

    I have a DTS package that runs at 5am with the following steps:

    1: Executes batch file to run a 3270 session connecting to a DB2 database, runs the report and exports the report to an FTP site

    2: Runs batch file for FTP the file locally (the DTS FTP locks up)

    3: Check to make sure the file was ftp'd over

    4: Delete the current data (relative to what I'm going to import)

    5: Alter a data column to a varchar

    6: Import the text file into the table

    7: Delete all records that do not have dates in the date field

    8: COnvert date column to smalldatetime

    9: Delete report file from local and ftp

    Unfortunately, I can only export reports using a gui (QMF Panel) and have to use a macro mimicing the manual process. The problems come in here, the DBAs and some higher (that run that particular DB) up have decided that when they are running quries or any other job that no one else can have access to the tables they are using. Therefore I will get an error stating that portion of the DB is in use; my query doesn't run and the report is not exported. I have a check to make sure that the report exists before continuing with the package (step 3).

    What I currently am doing, and I don't think it's the best way, is On failure (the file didn't ftp over because it wasn't created), I rerun the batch file (step 1) to try again ( this is about 15 minutes after the error) Now start the process again I execute the package again.

    That doesn't seem very efficient. Is there a way to say start again on failure? I tried pointing it back to the begining on failure but it doesn't work (probably because of the possibility of an endless loop)

    What would be the best way to restart the package x minutes later and perhaps only reattempt x times.

    Thanks for any help.

  • March 18, 2003 at 3:57 pm

    #451392

    I'm presuming the DTS Package is setup as a scheduled job. That being the case you can set the Retry attempts and Retry interval on the Advanced tab of the Edit Job Step window.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • March 19, 2003 at 5:41 am

    #451426

    That should work perfectly fine, thanks. I kept looking in the DTS package for an option like this, never thought to check in the JOB section

    Edited by - snaayk on 03/19/2003 05:41:25 AM

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

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