Post reply

Help with Script Task - Check if file refreshed and return success or failure

  • December 5, 2011 at 11:22 am

    #249215

    Hi,

    I have a package that pulls a csv file from a client's ftp server daily and load into a sql table. I want to be able to check if the file was refreshed from the prior day leveraging the ftp server's modified date time stamp before loading the file. All I need is for the script task to succeed or fail (If the file modified time is the current day the tasks succeeds and I flow into my data flow task else it fails and I flow into my email task). I do not need to do any logic in the script task just return success of failure.

    Thanks

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • December 5, 2011 at 12:25 pm

    #1416974

    MostInterestingMan (12/5/2011)

    Hi,

    I have a package that pulls a csv file from a client's ftp server daily and load into a sql table. I want to be able to check if the file was refreshed from the prior day leveraging the ftp server's modified date time stamp before loading the file. All I need is for the script task to succeed or fail (If the file modified time is the current day the tasks succeeds and I flow into my data flow task else it fails and I flow into my email task). I do not need to do any logic in the script task just return success of failure.

    Thanks

    Presumably the file always has the same name & that's what's causing you the problem?

    May I be the devil's advocate and ask what you would do if, one day, the job failed to run for some reason? Manually modify the date last modified, I guess.

    Anyhow, the way to do this is not quite as you suggested.

    Instead,

    1) create a package-scoped Boolean variable called 'ContinueProcessing' (or whatever).

    2) In your script task, set the variable to true or false depending on the date last modifed as you suggest.

    3) After your script task, create two expression/precedence constraints, one which goes to your data flow and one to your e-mail task. The two expression formulas will be checking the value of your variable (@ContinueProcessing==true and @ContinueProcessing==false, or similar - sorry, doing this without SSIS in front of me).

    That's it in outline - make sense, or would you like more detail?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • December 5, 2011 at 12:36 pm

    #1416984

    I'd personally approach this utterly differently, but a lot of that comes from the necessity of CYA.

    Archive the file with a different file name using a move. Even a sub-folder. You keep the old one in case a load went south or the client is arguing they gave you something and you can reference it, it helps for future usage, and it removes the problem of overwrites.

    It also makes sure, if you change a few permissions, that the client doesn't overwrite a file you haven't processed yet. It's yet another safety check.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • December 5, 2011 at 12:55 pm

    #1416991

    MostInterestingMan (12/5/2011)

    Hi,

    I have a package that pulls a csv file from a client's ftp server daily and load into a sql table. I want to be able to check if the file was refreshed from the prior day leveraging the ftp server's modified date time stamp before loading the file. All I need is for the script task to succeed or fail (If the file modified time is the current day the tasks succeeds and I flow into my data flow task else it fails and I flow into my email task). I do not need to do any logic in the script task just return success of failure.

    Thanks

    Thanks for your quick responses but I think I've found a solution leveraging a File Properties Task (http://filepropertiestask.codeplex.com/) from codeplex.com. The task retrieves and optionally sets properties on a file in the control flow. With this task I can retrive the file from the ftp server, capture the modified date and decided if it's the current date before processing.

    Thanks

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

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

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