• Home
        • Database Development

          Database development is designing, creating a database or data model, and analyzing requirements and their intents as raw data.

          Learn More
        • Architecture & Design

          Software architecture refers to the fundamental structures of a software system and the discipline of creating such structures and systems.

          Learn More
        • Programming

          Computer programming is the process of performing a particular computation or more generally, accomplishing a specific result.

          Learn More
        • Cloud Computing

          Cloud computing is the on-demand availability of computer system resources, especially data storage and computing power.

          Learn More
        • ETL Development

          ETL provides the foundation for data analytics and machine learning workstreams. Through a series of business rules, ETL cleanses and organizes data.

          Learn More
        • Data Visualization & Reports

          Data and information visualization is an interdisciplinary field that deals with the graphic representation of data and information.

          Learn More
  • Blog
  • Contact

Execute All Packages Within SSIS Project Catalog

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 set up 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 a master package (plus configure each package within an EPT). Thus, such an alternate still feels like = hard labor.
The best way to achieve our objective is via scripting.
So, what I have done is come up with a script that will help you execute packages using a 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.



October 13, 2022
Sifiso is Data Architect and Technical Lead at SELECT SIFISO – a technology consulting firm focusing on cloud migrations, data ingestion, DevOps, reporting and analytics. Sifiso has over 15 years of across private and public business sectors, helping businesses implement Microsoft, AWS and open-source technology solutions. He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg.

Meet Our Experts

We are proud to have a team of experts who are passionate about delivering the best possible solutions to you. Our team members are highly skilled and experienced in a diverse range of IT technologies, and are committed to staying up-to-date with the latest industry trends and best practices to deliver you the best results.


Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join Our Newsletter

Subscribe to get our latest and best thinking on the most definitive workforce topics affecting HR leaders and organizations today.