dcdanoland (8/13/2012)
You make very good points regarding Microsoft testing and the WMI Event Watcher Task. I guess my qualm is that it seems to be an afterthough by Microsoft. All the other tasks are designed to not run continuously, and SSIS's architecture seems designed for such tasks. It doesn't seem as if SQL's architecture is really designed for continuously running tasks. Otherwise they would provide more support for that would be somewhat like SQL Agent.
I agree, it can seem that way. Running packages continuously is typically not the first usage pattern that comes to mind when designing a process. In my experience the goto for batch processing and ETL design is usually some variation of:
1. start a package that checks for the existence of a file
2a. if the file is there load it and exit
2b. if the file is not there exit
Then a SQL Agent job will be setup to run this process once per minute, or once every few minutes, during a time period when the file is expected to arrive. The pattern is intuitive, is tried and true, and does work fine in most cases but adds a lot of transient overhead to the server in terms of CPU, memory allocations/deallocations as well as msdb activity as is detailed in the article. Some doubts about this same topic were (hopefully) laid to rest in the comments of my WMI Event Watcher Task article as well if you have a moment to read through those.
The precedence for running an Agent job continuously was set long ago as well, by Microsoft. In the SQL Server Replication subsystem the Log Reader and Distribution Agents are always running per a SQL Agent job that is created when replication is configured. Again, it may not be intuitive to have a job that runs an SSIS package continuously but in my opinion there is nothing inherently wrong with the design pattern and in some cases it may be the most suitable option.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato