SSIS, SQL Server Agent and WMI

I need to hang out with Donald Farmer more often. He knows more about SSIS than I will ever forget – or something like that.

Earlier today I co-presented another interactive theater session (largely a repeat of the session we did last week during the developer conference, but with some new content and a different vibe) with Donald and (as before) I learned a few things about using SSIS that I had never seen before. One of them has to do with one of the most popular[1] SSIS topics: SSIS and SQL Server Agent.

This lesson doesn’t actually have much to do with SSIS – it has to do with how you trigger the execution of a SQL Server Agent Job. Normally SQL Server Agent Jobs are executed on a schedule, so that the same packages run at the same time every day. To be honest, this is the only way I have ever used SQL Server Agent. But (as I learned today) you can also configure your SQL Server Agent Jobs to be triggered by Windows Management Instrumentation (WMI) events as well.

What does this mean?

It means that any events that are raised by the operating system (or other software that uses WMI) can be used to trigger the execution of your job and your packages. Think about having packages that automatically run when your processor utilization drops below a specific threshold as an example. The possibilities are endless[2] and the setup is documented in SQL Server Books Online here: http://technet.microsoft.com/en-us/library/ms366332.aspx

Enjoy!

 

[1] At least if the web traffic reports for my blog are to be believed.

[2] This is a nice way of saying that I don’t know enough about WMI to come up with more examples without making a fool of myself.

Advertisements

About ssimagine

My name is Matthew Roche, and I am a Senior Program Manager with the SQL Server product group at Microsoft. I work on Master Data Services and Data Quality Services, and have previously worked on SQL Server Integration Services. Although I work for Microsoft and will be posting on technical topics, I want to stress that this is a personal blog, and any opinions posted here are mine and mine alone. I built my career around SQL Server and Microsoft technologies for well over a decade before I joined Microsoft as an employee, and I plan on using this blog to share my personal experience and opinions. They may well be shaped by my experience on the SQL Server team, but they’re still mine, and not that of Microsoft, disclaimer, disclaimer, etc., etc..
This entry was posted in BI, Deployment, SQL Server, SSIS. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s