Post reply

Can i call a SP Automatically as soon as we receive a file to upload???

  • February 9, 2005 at 11:35 pm

    #60691

    Hi all,

    I want to automate file Uploading, I have a SP which takes care of uploading of file from a windows directory to DB. Can i automate it. I want to Execute the SP as soon as we receive the file.

    I have other option to run a job on periodic basis. But I want to Execute the SP as soon as we receive the file. Is this Possible from any DB tools or any other method.

    If Yes pls. Suggest.

    Thanks

    Raju

  • February 10, 2005 at 3:14 am

    #541534

    If you were the sp, how would you then know if the file has arrived?

    Either someone tells you, or you just go yourself and have a look, right?

    So, either you make so that the process that puts the file there also starts the sp when the file is in place, or you set up  a job that on a periodic basis checks if there is any file to load.

    /Kenneth

  • February 10, 2005 at 10:41 am

    #541627

    Why not use the filesystemobject to see if a file exists and execute your sp every few minutes. You can use the result of the filesystemobject check to exit the sp. If successful, write to a dummy table the date run and then your sp can check this as well to exit the code if need be.

    I did read somewhere lately about a filewatcher, but am not to sure if it will help you out?

    ------------------------------
    The Users are always right - when I'm not wrong!

  • February 10, 2005 at 10:50 am

    #541630

    What are the requirements, regarding latency ? Is it really necessary to load the file almost instantaneously, or is a delay of several seconds acceptable ?

    The requirements drive the solution - near-instantaneous requires something event driven, which would require a filesystemwatcher component. Otherwise you can poll periodically, for which a scheduled DTS package launched every N seconds is probably adequate.

    Beyond that initial design decision, there are multiple ways to skin the cat, ranging from a Windows filewatcher service, to DTS, to using xp_cmdshell to DIR the folder.

  • February 10, 2005 at 9:07 pm

    #541714

    Thanks Kenneth, Jonathan Stokes and PW.

    Thanks for uor Valuable suggestion.... The problem here is i don't know when the File is going to arrive.... Because its totally different system (legacy system or SAP)... But one thing is clear, i will have to use scheduled DTS or any other scheduling to check for file and do the task accordingly..This will workout...

    Thanks again 

  • February 11, 2005 at 1:47 am

    #541732

    I do this kind of stuff by just scheduling a job in SQL Server - works for me

    Just a note on files... You may want some way to verify that the file is complete before you try to load it. Thing is, that if you dir it or similar, a file will show up as existing and with it's full size, but may in fact still be 'loading' - ie the copy isn't finished.. This may or may not be an issue. If it's decided it may be an issue, there are a few ways to deal with it. A common method is to have an accompanying 'semaphore-file' that by it's existence signals that the 'main' file is fully transferred. In such cases, you need only check existence of the semaphore-file to know that there is a datafile to load.

    /Kenneth

  • February 11, 2005 at 9:04 am

    #541826

    Your task will get easier in the new DTS (now called Integration Services) in Yukon/2005 when it is released.

    eg: http://www.sqlis.com/default.aspx?23

     

     

  • February 11, 2005 at 9:47 pm

    #541922

    Hi Kenneth & PW,

    Thanks for uor suggestion.

    Kenneth,

    I am not too much about 'semaphore-file'  and How to use this. Thanks if you can help me out. I was not thinking on the Problem identified by you.

    Thanks

  • February 14, 2005 at 2:41 pm

    #542156

    RE:

    ... You may want some way to verify that the file is complete before you try to load it. Thing is, that if you dir it or similar, a file will show up as existing and with it's full size, but may in fact still be 'loading' - ie the copy isn't finished.. This may or may not be an issue. If it's decided it may be an issue, there are a few ways to deal with it. A common method is to have an accompanying 'semaphore-file' that by it's existence signals that the 'main' file is fully transferred. In such cases, you need only check existence of the semaphore-file to know that there is a datafile to load.

     

    I update a database from multiple text files that are ftp'd in every weekend.  I am sent a trigger or 'semaphore-file' for each text file when it completes...  but... some of these files are up to 8 gb and even though the trigger file is sent and received, windows still has not released the text file and I can't access it.  The way I work around this is by attempting to rename the text file to it's own name after the trigger file arrives.  I loop this until it succeeds...  If you are working with smaller files, this may not be an issue.

  • February 16, 2005 at 5:15 am

    #542501

    The idea with semaphore files, is that the sender sends a specially named file after the sender has completed the transfer of the data file. This way, the receiver needs only look for the existence of the semaphore in order to know if the transfer is complete or not.

    /Kenneth

  • February 16, 2005 at 6:10 am

    #542512

    Thanks kenneth and Everyone for support.

Viewing 11 posts - 1 through 10 (of 10 total)

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