Thank you for reading the article and for posting these questions.
Some questions to consider:
1. What kind of Windows Server security permissions does the identity running the SSIS package need in order to execute the WMI query? In a real world environment, I'd assume the file would be dropped to a separate server (maybe a network filer) which is where the WMI connection would need to be pointed, and my SSIS identity is going to be running with the absolute least privileges needed.
By default on Server 2008 R2 the Authenticated Users group has permission to connect to the \root\cimv2\ WMI namespace. You can check the namespace permissions using the WMI MMC snap-in (WmiMgmt.msc located in C:\Windows\System32). As I understand it, once WMI namespace security checks are complete WMI does no further security checks and standard Windows security becomes relevant. The article demo was completed as a local administrator but I just ran some tests as a non privileged user and from a WMI perspective I did not need to configure any special permissions to access the event class in the \root\cimv2\ namespace on localhost. To run the demo package however, because a file is being manipulated in the file system later in the process I did have to make sure the non-privileged account running the SSIS package had the file system permissions necessary to carry out those actions.
In terms of watching files on a network server I know of two options, and there may be more:
1. You can map a drive and refer to the drive letter in your WMI query. I refer to this scenario in Step 1 of the article demo. While mapping a drive may not be practical in a real world scenario for most, for some it may be an option.
2. You can configure your WMI Connection Manager to connect to a remote server. Attaching to an event class on a remote server was out scope for this article however you can see in Step 9 of the demo where I make reference to the option. Have a look at the WMI Connection Manager (SSIS 2012) for details on how to attach to an event class on a remote server. Note that the job runner's permissions on the remote server would need to be configured properly.
2. What is the impact to your SQL server if you have a long-running SSIS package (inside a SQL agent job, presumably) like in your example and you need to either restart the machine or the SQL Agent service? .NET Windows services have events you can handle in your code that allow you to gracefully stop your file watcher. Will the restarts hang when the SSIS package is sitting there? Do you have to manually abort the SQL Agent Job?
3. If a file doesn't get dropped for a couple days, what is the impact to the server to having a SQL agent job running for several days?
Addressing questions 2 and 3:
I do not anticipate any issues with an approach where a job is running at all times the SQL Server Agent service itself is running. There is a long-standing precedence for such a scenario when it comes to SQL Server Replication. Jobs that run the replication Log Reader and Distribution agents may run at all times while supporting continuous transactional replication subscriptions. The replication-job schedules are of type 'Start automatically when SQL Server Agent starts' and the jobs steps never complete, i.e. they are running at all times while the SQL Server Agent service is running.
If it was the intention of the ETL process designer to have a SSIS package watching for files at all times the same could be done in terms of the scheduling the job to start when the SQL Server Agent service started. An auxiliary schedule or schedules would be required so the SQL Server Agent would start the job each day, if it were not still running, prior to the earliest anticipated file delivery time. A nice thing about SQL Server Agent is that it will not start multiple instances of a job, i.e. if a job is running and another scheduled execution time arrives that schedule will be skipped. A schedule secondary to one which starts the job when SQL Servrr Agent starts would account for the operational scenario when the package finds a file, loads it and ends gracefully yet we want that job to again start watching for the file once again.
Regarding stopping a job, or restarting SQL Agent, I tested this and SQL Server Agent has no issues ending a job running a package via DTExec.exe that uses the WMI Event Watcher Task. If your job were configured with a schedule of type 'Start automatically when SQL Server Agent starts' it would begin watching as soon as the service was brought online again.
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato