A new task in SSIS is the WMI Task. Actually, there are two tasks. One is the WMI Event Watcher task and the other is the WMI Query Task. They are fundamentally different tasks even though they use the same underlying technology, WMI.
WMI is Windows Management Instrumentation. Way back when I was working in the eHome group, I found out about it the hard way. I had just written the fundamental infrastructure for a mechanism to discover other machines on the home network and to discover information about the machines like machine name, hard drive space, disk drives and attached printers etc. Then someone came along and said, hey, you should check out this WMI stuff. It's pretty cool. He wrote a little web page that worked against the WMI scripting model in about half a day that did exactly what I had taken a week to do. And, he already had a web page UI. :(
Well, that was my rude awakening to WMI. I hated it then cuz it meant all my hard work was down the drain and I really was looking forward to that project. WMI can be used to find out just about anything about a machine on a network. Right down to the MAC address and remaining hard drive space available to the kinds of expansion cards in the machine.
I've built a package that uses the WMI event task to do something really simple. Basically, it sits and waits on a directory for a file to be created. And then it returns. I've put it all in a loop that could well be a forever loop. This is not considered best practice, of course. It's just a simple demonstration to show the WMI task in action. Basically, it waits for a file to show up in the E:\temp folder. When it does, it returns and a script task is executed, then it returns to waiting for the next file to show.
You can get the package here.
For those that would rather just build the package themselves, here's the WMI Query:
SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE Targetinstance ISA "CIM_DirectoryContainsFile" and TargetInstance.GroupComponent= "Win32_Directory.Name=\"e:\\\\temp\""
You can change the directoryname to something different, but it must be formatted this way.
Now, this doesn't wait until the file is completely written. So for very large files, if you attempt to write or otherwise open the file too soon, it will fail. The way you may work around this is to have whatever process copied the file, to also copy another file with the same name, zero length and a different extension after the actual file is transferred. Then, you can enumerate or open any file with the same name as the zero length file using the correct extension and be assured that the file is completely copied.
Give it a try, let me know what you think. See if you can find other uses for the WMI Task. Here are some we've thought of:
- Detecting low memory conditions at the beginning of a package and alerting via email to the issue
- Detecting full disk volume conditions before beginning large copy operations
- Detecting email ariving to a particular recipient with a particular subject
- Detecting the availability of another server machine before attempting to move files
- Waiting for an existing memory intensive process to complete before beginning another
WMI is very powerful, but mostly what keeps people from using it is the learning curve. It's substantial for sure, but provides some really robust and elegant ways of doing things that are otherwise quite difficult or as I discovered a few years ago, require a lot of coding to accomplish.
Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden