Starting a job based on external source/event

[sdives]

We run a weekly update on one of our databases from a download (FTP) from our mainframe. What I want to do is start the update job automatically after all of the files are downloaded.

I have a similar process in another database where the users set a flag in an application after they've made certain changes. The job fires daily at 18:00 and checks the value of the flag in the table. It then processes or goes back to sleep. This works great but there is no flag that the users can update in this application.

Thanks in advance.

Sidney Ives
Database Administrator
Sentara Healthcare

[andi_g69]

You might want to do it like this:

1) Write a batch file which checks for the existence of all the required files (Can also be an ActiveXScript). It should return 0 if all files are there, otherwise return any errorlevel different from 0

2) Create a SQL Job with the first step being the batch from 1)
On Success (means return 0) goto the next step which is your loading routine (DTS or whatever you use)
On Failure just skip the job

3) Schedule the job to run every hour (or more or less frequent)

BTW: In order to make sure that your import does not start while the last file is being transferred it is a good practice to transfer an additional -dummy- file at the end. This will be your indication that the transfer of data is finished and in this case you only need to check for the existence of this file

[dim]

Depends on how you downloading your files. If you can initiate the download from a command prompt, then this can be done by issue xp_cmdshell stored procedure as the first step of the whole import process with the later usage of the downloaded files in your following steps.

Provide more details about the FTP job you use to get more specific answer, pls.

Dim