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.
Demo project
I can manually execute Package A as shown below:
Manual execution of Package A
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 come 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:
Stored procedure created
You can then execute the script by providing the name of the project and folder whose packages you wish to execute.
Now going back to the 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 results
Script execution confirmation message
Have a look at the script and let me know what you think.
Cheers!
CodeProject
Sifiso W. Ndlovu is a certified Microsoft professional who holds a Master’s degree in IT Management from the University of Johannesburg. He specializes on a range of enterprise and consumer technologies using open source and proprietary software. He is the member of the Johannesburg SQL User Group wherein he has made several presentations on User Group Meetings and SQL Saturday sessions. He has written for a number of publications including SQLShack.com and SQLServerCentral.com.
Submit an article or tip
