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

image

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

image

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.

image

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.

image

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

image

Reference:

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

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

1 comment:

  1. 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)?

    ReplyDelete