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.

How to schedule a package the safe way with SQL Server Agent

Case
An administrator of a shared Microsoft SQL Server Integration Services machine wants to run the various packages with different user accounts via the scheduler of SQL Server Agent. He has created separate users for each department and gave them all only rights for there own windows folders.

Solution
1) Goto Microsoft SQL Server Management Studio (SSMS) and create a new Credential (under Security).

SSMS, Security, Credentials

2) Give the credential a suitable name and select the applicable windows user and enter the password

New credential

3) Create a new Proxy account (under SQL Server Agent).

SSMS, SQL Server Agent, Proxies

4) Give the proxy a suitable name and description and select the applicable Credential. After that select SQL Server Integration Services Package (that's the only thing this proxy may do).

New Proxy account

5) Create a new Job (under SQL Server Agent).

SSMS, SQL Server Agent, Jobs

6) Give the Job a suitable name on the General tab. Goto the Steps tab and create a new step. Give the step a suitable name. Select SQL Server Integration Services Package as Type and the most important: select your Proxy account under Run as. And of course select the package you want to run. There are a lot more options on this screen. Feel free to try them out.

New Job, New Step























7) Now add a new Schedule on the Schedules tab. Give it a name and select the desired schedule. This one starts each night at 0:00.

Each night at 0:00


























Repeat all the steps for all the departments and they won't bother each other.