File Copying - Best Approach

  • Please can I have comments on the best approach to a piece of work I have to complete. Here is the situation:

    Each day we receive files from another system. These files are moved and processed by DTS jobs that we run each day. I have a new requirement to send copies of these files to another server. There are two approaches I can think of:

    1. Change my ActiveX scripts that move the files to also do a file copy.

    2. Create a polling job that runs (e.g.) every 5 minutes to monitor the receiving folder and copy files, with conditional code in an ActiveX script to not copy the file if it has already been copied by referencing a log table.

    Approach 2 means delivering copies of files much quicker and I have a lot less work to do changing my current loading programs. However I am concerned about the overhead of a polling job running every 5 minutes of so.

    Does anyone have experience of this kind of situation? Any help appreciated.

    Alan

  • I don't see how adding a line of code to do a copy is longer than recreating a process, but anyhow...

     

    Checking for the existense of a file every few minutes is not gonna be much of a hit on the server... assuming that the query is well written and you don't have 1 M files to check for existense .

  • I concur with Remi... a simple polling job is not going to be any load on the server at all.  We have a couple that run using the undocumented but very handy xp_DirTree proc (available in SQL Server 2000 and 2005) and they take scant milliseconds to run...  even a DTS job that does the polling isn't going to be slow by any means provided it's written well, as Remi suggested.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks guys.

    It's going to be easier to create polling jobs that monitor about 6 receiving directories.

    If I attempt to change the 30 or so packages that process the data daily, there is a risk that the whole job could fail, so polling is the option I'm going to go for.

    Thanks again.

    Alan

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

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