File Copying - Best Approach

Question

File Copying - Best Approach

AlanB

SSC Eights!

Points: 875
More actions
September 1, 2006 at 9:28 am

#94359

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

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

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 .

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 2

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)

AlanB SSC Eights! Points: 875 More actions · Answer 3

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