Post reply

Dynamically rerun DTS package

  • February 12, 2008 at 7:23 am

    #378512

    Hi,

    I have a DTS with a FTP task importing files from a remote site. Most of the time, it works fine, but when the FTP site in unavailable, or the internet connexion is down, the DTS fail. But I absolutly need these files, so, I want the DTS to be automatically reschedule to try again later. How I do that?? Can the DTS reschedule itself on failure, and, if it succeed, reschedule it back to the first run time?

    thanks a lot for your time,

    Dominic Gagné

  • February 12, 2008 at 10:23 am

    #777431

    DTS doesn't run or schedule anything. SQL Agent does so.

    I'd suggest that you run a task to reschedule the job if it fails. SQL Agent can detect a failure and take an action based on that. When it fails, have a step in your job to create a new schedule in xx minutes to re-run the job.

  • February 12, 2008 at 10:51 am

    #777448

    That is the very reason why I don't use the lousy FTP task. Instead, I create an Active X Script task that executes a shell command to download the file.

    I've posted a code example here: http://www.sqlservercentral.com/Forums/Topic444266-19-1.aspx

    If you're downloading a specific file, replace the "mget *" in my example with "get nameofyourfile.txt".

    I would add a 2nd Active X Script task that would use the FileExists function of the FileSystemObject to verify that the file exists on your network. If it doesn't exist, execute the FTP download Active X Script task again.

  • February 12, 2008 at 12:02 pm

    #777479

    thanks for your answers.

    I came up with the idea to build an application in VB.NEt (a language I'm far more at ease than SQL) as a service and call the DTS from within the app, check the result and relaunch the DTS if it fails at X minutes later. Is it something more reliable?

    thanks.

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

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