The SSIS Project Deployment Model is great! It really is the best thing since sliced bread! I specifically appreciate the package execution logging and reporting that is provided by this model but …. (there is always a but :), right), lately I’ve been looking for ways to execute all packages within a project at once.
For instance, have a look at the below Demo project: it consists of Package A and Package B.
I can manually execute Package A as shown below:
That’s great if I only wanted to execute Package A. What if I want to execute all packages?
Unfortunately, you don’t have the “execute” option in the project:
missing “execute” option at project level
So what happens if my project had 99 packages and I needed to execute all of them at once? (As is sometimes the case in data warehousing environments whereby you need to extract and stage data from disparate sources).
The current implementation would be to navigate to each package and execute them one at a time = hard labor.
Alternatively, you could setup a master package that you could have used to execute each package using Execute Package Task (EPT) – but that would still mean that you have to add 99 EPT controls into master package (plus configure each package within an EPT). Thus, such an alternate still feels like = hard labor.
The best way to achieve out objective is via scripting.
So, what I have done is coming up with a script that will help you execute packages using looping mechanism in a form of T-SQL Cursor.
You can find a copy of the script here: sp_ExecuteAllPackagesWithinProjectCatalog
All you need to do after downloading the script is to execute it (hopefully not within system databases). This will create the stored procedure for you as shown here:
You can then execute the script by providing the name of the project and folder that whose packages you wish to execute.
Now going back to above example, when I execute the script – both Package A and Package B are executed plus I also get a confirmation under the message tab:
script execution confirmation message
Have a look at the script and let me know what you think.
Cheers,
Sifiso.
