dcdanoland (8/13/2012)
Thanks for the article. I've been intrigued by this component, but have shied away from it because I have qualms about the architecture. I worry about having an SSIS job run continuously. I just don't think SSIS was designed to run that way. I doubt that Microsoft tests this capability.
I must respectfully disagree with your assertion. I can only offer you indirect evidence since I do not work for Microsoft but the example provided in the WMI Event Watcher Task documentation implies they expect an SSIS package might be run continuously:
From WMI Event Watcher Task (SQL Server 2012):
The following query watches for notification that the CPU use is more than 40 percent.
[font="Courier New"]SELECT * from __InstanceModificationEvent WITHIN 2 WHERE TargetInstance ISA 'Win32_Processor' and TargetInstance.LoadPercentage > 40[/font]
The following query watches for notification that a file has been added to a folder.
[font="Courier New"]SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE TargetInstance ISA "CIM_DirectoryContainsFile" and TargetInstance.GroupComponent= "Win32_Directory.Name=\"c:\\\\WMIFileWatcher\""[/font]
dcdanoland (8/13/2012)
What happens if an error occurs and the package ends prematurely? I think the package would have to be manually restarted.
If the package ends prematurely then you would need to start the package again. Ideally a package will never end prematurely, however if it does it either means the exception handling needs improvement or manual intervention is intentionally provoked, or in some cases both.
dcdanoland (8/13/2012)
As much as I like doing things in SSIS, I think we'll end up writing a Windows service in .NET because it will give us more control in a more standardized architecture. Unfortunately, writing a service will be a lot more work. I guess I could be persuaded to use the FileWatcher task within SSIS, but I would need real-world results and a thorough discussion on the architectural pros and cons of using the task.
I am of the opinion that there is nothing inherently wrong with running SSIS packages continuously. The example I showed above taken from the Microsoft documentation ought to be sufficient to show that it was one intended use of SSIS, however feel free to disagree.
If I break it down SSIS is nothing more than a set of XML instructions (open a dtsx file in Notepad). The runtime (DTExec.exe) simply interprets those instructions. So, when I think about running SSIS continuously I'm really thinking about running an instance of DTExec.exe continuously and DTExec.exe is an executable just like any other executable. It is subject to memory leaks, runaway threads, unhandled exceptions, race conditions, etc. however I am not too concerned about it given the level of effort Microsoft has put into developing and testing it. Couple that with the number of users in the community helping to solidify its usage patterns and report bugs. I am aware of some memory leak issues in SSIS 2005 with the Foreach Loop Container as well as the Execute Package Task but those were quickly corrected. There are many people using those tasks (and the other built-in tasks) therefore the problem surfaced quickly and a fix was produced by Microsoft.
In the case of the Konesans File Watcher Task, while it is not produced by Microsoft its central functionality is built around a class in the .NET Framework, FileSystemWatcher Class, which brings it into the same domain as other built-in SSIS tasks in terms of being backed by Microsoft.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato