SSIS: How to automatically rerun a package after it has completed?

Question

I need my package to run again directly after it has finished. How to do it ? Should I use the SQL Server Agent ?

I need to do it this way because the user won't have access to SSIS, but only to Management Studio.

I used a WMI Event Watcher Task to look indefinitely for a particular file being droped in a folder "Drop". When it happens, the package executes, and then I uses a File System Task to remove the file from the folder where it was dropped to another folder "Archved".

Finally, the user should be able to rerun the package by dropping the same file in the folder that triggers the package "Drop".

My problem is that I have to click on "run package" after it has finished. I would like ssis to do it by itself.

Is it possible?

EDITED:

When adding a For Loop Container with EvalExpression 1=1, the following two Execute SQL Tasks never run. Without the Loop they do but only once straight to the end.

Answer 1

I think that the easiest approach to achieve that is to create another package, lets call it master package. Add two Execute Package Task that execute the same package.

For more information about Execute Package Task, check the following link:

• SQL SERVER – The Basics of the Execute Package Task

Answer 2

This can be setup using SQL Agent something like this:

Step 1: Create a new job using SSMS:

Step 2: Add a new job step to run the SSIS package

Step 3: Add a new schedule to define the interval for the job run (adjust it to meet your requirements)

In this case, for each run, the file watcher step in the package would wait for the file to be dropped and run the job successfully once the file is dropped/found.

Note: Please test this on your non-prod environment to ensure it works as intended.