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

6 comments:

  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
  2. Thanks for sharing such wonderful information about SQL Jobs.I read your articles very excellent and the i agree our all points because all is very good information provided this through in the post.

    ReplyDelete
  3. In the wake of perusing such astonishing works, your heart gets empowered.
    https://www.resumeyard.com

    ReplyDelete
  4. I wanted to thank you for this excellent read!! I definitely loved every little bit of it. I have you bookmarked your site to check out the new stuff you post. HR consultants

    ReplyDelete
  5. Excellent article. Very interesting to read. I really love to read such a nice article. Thanks! keep rocking. Calgary HR consultant

    ReplyDelete
  6. Your work is truly appreciated round the clock and the globe. It is incredibly a comprehensive and helpful blog. high quality Recirculation Valve

    ReplyDelete