Saturday, 25 December 2010

Continuously watching files with WMI Event Watcher Task - Stopping the infinite loop

Case
Recently I explained how create an infinite loop with a WMI Event Watcher Task in it to watch for new files throughout the day. That solution had one small disadvantage: Not only the loop runs infinite, but the package does too... and some people don't want that. They want to end the package at the end of the day and restart it the next day.
Previous solution





















Solution
We will stop the package just before midnight and restart it again at midnight.

1) Add timeout to File Watcher
You can't interrupt the loop and end the package because the WMI Event Watcher Task is still watching out for new files. It will only stop if you drop a file in the folder, but you can also add a time-out to continue the control flow. Let's add a 14 minute time-out (840seconds) and let the control flow continue without errors:
  • Set ActionAtTimeout to Log the time-out
  • Set AfterTimeout to Return with success
  • Set Timeout to 840 seconds
Timeout and continue




















2) Change loop EvalExpression
After the timeout from the WMI Event Watcher task, the Foreach loop won't find any files. So the loop restarts.
To stop looping just before midnight, you have to change the EvelExpression of the loop to:
GETDATE() < DATEADD("Mi",45,DATEADD("Hh",23,(DT_DBDATE)GETDATE()))
Continue while GetDate is smaller than today at 23:45


















Note: You can finetune the timeout and the endtime of the loop to create a smaller window.

3) Schedule package
Schedule the package to start each day at midnight.


All roads lead to Rome... So let me know what your solution is.

2 comments:

  1. What if we want to restart it at 11PM as opposed to midnight? do we add another condition to EVAL expression?

    ReplyDelete
    Replies
    1. Let it stop a couple of minutes before 23:00
      GETDATE() < DATEADD("Mi",45,DATEADD("Hh",22,(DT_DBDATE)GETDATE()))

      Delete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.