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.

Reference:

http://msdn.microsoft.com/en-us/library/ms190084.aspx

http://msdn.microsoft.com/en-us/library/ms186510.aspx