Click here to Skip to main content
13,588,575 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

3.9K views
38 downloads
Posted 6 Jul 2015
Licenced CPOL

Execute All Packages Within SSIS Project Catalog

, 6 Jul 2015
Rate this:
Please Sign up or sign in to vote.
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

1

I can manually execute Package A as shown below:

Manual execution of Package A

2

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

3

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

4

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

5

Script execution confirmation message

6

 

Have a look at the script and let me know what you think.

Cheers!

 

 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sifiso W Ndlovu
Technical Lead select SIFISO
South Africa South Africa
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.

You may also be interested in...

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01 | 2.8.180615.1 | Last Updated 6 Jul 2015
Article Copyright 2015 by Sifiso W Ndlovu
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid