Post reply

SQL job problem

  • October 18, 2007 at 5:57 am

    #118968

    Hello everyone, I'm new here and I'm gonna jump straight into the deep end!..

    I have a SQL job that runs a cmdexec every hour.. which FTPs files from a specified directory and archives them regardless of the files being sent successfully or not.

    My problem is the not part!...

    Is it possible to have the job setup so that it FTP's the files and if this fails will try again before archiving them?

    at the moment the job is set up as below:

    e:\ftpscript\FTPScript_v1.7.exe /src E:\"folder"\ /archive e:\"folder"\archive /log e:\"folder"\log

    and alot of the time I get an error saying:

    "Upload startedUnrecoverable error: Transfer failed: "/exchange" <-

    "e:\"folder"\archive\2007-10-17_10-01-17"

    so I would have to go in and manually move the files back to the FTP folder and kick off the job manually, which normally works the second time..

    I guess I'm after a retry of some kind ?

    Thanks everyone for any help you can give.

    Gaz.

  • October 18, 2007 at 7:00 am

    #743103

    Hi,

    May I suggest an alternative way?

    Write an SSIS package that includes an FTP task. Set the number of Retries on the FTP Connection to 5.

    You may also have a step to Zip teh filw before FTP and have its green arrow (Success) flowing from it into the FTP task so that it will zip then FTP.

    I hope that helps,

    Osama

  • October 18, 2007 at 10:25 am

    #743215

    Osama's suggestion sounds pretty good, although it would be a DTS package rather than SSIS since this is SQL 2000.

    You can also set retries on a job step. Click on the Avanced tab of the step in job properties. You can set retry attempts and the interval between retries. You can also set the "On failure action" to skip the archive step if the FTP step failed.

    Greg

    Greg

  • October 18, 2007 at 12:08 pm

    #743249

    Make sure you TEST that the file is there. A lot of FTP clients don't report timeouts very well (meaning - I started to DL the file, and the connection times out/drops, and not error status).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

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