multiple file loading design problem

[pandatime]

Hi,

I am just looking for some input on designing a load process. I am not using SSIS.

Basically I have a three files that arrive on an ftp server around the same time each day. But the time of arrival is staggered to some degree, say between 7 and 7:30 am.

I want to build a more robust process for loading these files. There are two steps so far:

1. ftp the files
2. import the file using bulk load

My question is, would it make the most sense to create a separate job for each file? (Each job calling the ftp and bulk load stored procs). Because I want the loads to run independently of one another, that is, if a given file isn't available on the ftp site yet, that shouldn't stop the others from loading. I also want to separate out the error handling for each file, and be able to put retries on the ftp job step (the one that calls the ftp stored proc).

Right now, it's a lot of grief to try to do all of the above when one script is handling all three files at once.

The downside of course, is having to maintain three sets of scripts and jobs for each file.

Any ideas, suggestions, or feedback?

Thanks!

 

[simian336]

Maintaining 1 script is always perferable to 3.

Not sure why you couldn't use one script and pass it the file you want to process?

Simi