Dan Crowell


I want to watch a directory 24 hours a day to launch SSIS packages as files arrive. There are several options I have found to accomplish this:

  1. Use the WMI Event Watcher task in SSIS to launch the packages as files arrive. Leave the package with the WMI Event Watcher task running all day long everyday
  2. Create a Windows service that uses WMI to detect file arrival and launch packages
  3. Schedule SQL Server Agent to run the package when a WMI event is raised

What is the best practice Are there better approaches that I have not listed





Re: Best practice for WMI Event Watcher task

Phil Brammer


What about the File Watcher task http://www.sqlis.com/23.aspx






Re: Best practice for WMI Event Watcher task

Danny Crowell

I believe the File Watcher task runs within an SSIS package. This would require the SSIS package to run 24 hours a day, correct Is that a good idea





Re: Best practice for WMI Event Watcher task

Phil Brammer

Well, how do you propose to use a WMI Event Watcher task inside SSIS if that package isn't running 24 hours per day





Re: Best practice for WMI Event Watcher task

Danny Crowell

Why would I want to propose that since it is probably not a good idea

What is the best practice for watching a directory for files 24 hours a day I listed the 3 options that I know of. Let's get back to the orignal question in the thread.






Re: Best practice for WMI Event Watcher task

Phil Brammer

Danny Crowell wrote:

Why would I want to propose that since it is probably not a good idea

What is the best practice for watching a directory for files 24 hours a day I listed the 3 options that I know of. Let's get back to the orignal question in the thread.



Right, well, if you can do option #1, then why can't you use the File Watcher Task





Re: Best practice for WMI Event Watcher task

Danny Crowell

You are correct; option #1 is possible with a File Watcher task.

Are you saying that option #1 is the best practice I am skeptical about that since I do not think SSIS packages are designed to run 24 hours a day.






Re: Best practice for WMI Event Watcher task

jwelch

There was a memory leak in earlier versions of SSIS, but I believe it was corrected in SP1. So, in theory, you should be able to run a continous ForEach loop with a File Watcher task. In reality, I'd probably set the package up to be recycled once or twice a day, just to be safe.




Re: Best practice for WMI Event Watcher task

Subhash

It would be of great help, if you have a pointer to the way of achieving this using a ForeachLoop with a File Watcher task. What do we mean by the setting the package to be recycled

Thanks

Subhash Subramanyam






Re: Best practice for WMI Event Watcher task

Subhash

Subhash512525 wrote:

It would be of great help, if you have a pointer to the way of achieving this using a ForeachLoop with a File Watcher task. What do we mean by the setting the package to be recycled

Thanks

Subhash Subramanyam

Jwelch,

I got the link that shows how to use a File Watcher task in a ForEach Loop to monitor the newly arrived files or changes done to the existing files. So What do we mean by the setting the package to be recycled






Re: Best practice for WMI Event Watcher task

jwelch

By recycling, I'm refering to stopping and restarting the package at some defined interval. While you could set up the file watcher in an endless loop, I would have some defined end condition for the loop.




Re: Best practice for WMI Event Watcher task

Danny Crowell

After researching this for the last day, I have decided that the best approach is to:

  1. Create a Windows Service
  2. Use System.IO.FileSystemWatcher to monitor the directory and subdirectories for changes
  3. Launch SSIS packages programmatically from the Windows Service

I plan to write an article about this. If you are interested I will post a link in a week or so.






Re: Best practice for WMI Event Watcher task

jwelch

That would be great info to share with the community. Looking forward to it.




Re: Best practice for WMI Event Watcher task

Subhash

This would be the best approach, as it saves memory ..looking forward for your article on how we can achieve this..

Thanks

Subhash Subramanyam






Re: Best practice for WMI Event Watcher task

Danny Crowell

Combining the information in these articles yields the desired functionality.

To see how to create a Windows Service that watches a directory for changes:

http://msdn.microsoft.com/msdnmag/issues/01/12/NETServ/

To execute SSIS packages programmatically

http://www.codeproject.com/useritems/CallSSISFromCSharp.asp