Wednesday, March 27, 2013

Trigger SQL Agent Job by Files

Wondering how to trigger a SQL agent job when the file is ready to be processed? The simple and easy way to trigger a SQL Job after a file dropped into a window directory is attaching a SQL alert to the SQL job.

Here are the steps:

1. Open the job property window for the existed SQL agent Job.

2. Click on the Alert Node

3. Click on Add button to open new Alert window

4. Select WMI Alert Event type and set the property for Namespace and query

5. Click on OK to attach it.

Once file dropped in the folder, the SQL job will be triggered. The example I used will insert a row to [dbo].[FileProcessTime] table.

you may go to the Job property->History Page for the Alert or Query [msdb].[dbo].[sysalerts] table to check the the Number of occurrences and Last execution Time.

Sometime you may consider to delay the response for large or frequent files, you could go into the Option page to set it.

1 comment:

Very interesting blog post! I am actually trying to move my sql agent jobs from time scheduled to event scheduled. With one file to kick off the job it seems quite easy to configure. But how would you implement a dependency of two files meaning the job can only be started if both files were created (there is no order of creation)?