Adding an SSIS Application to SSIS Framework Community Edition

Note: This post was originally posted at SQLBlog.

I’m writing more about SSIS Framework Community Edition because lots of people are using this free, open-source utility to execute collections of SSIS packages – called SSIS Applications – in their enterprises. Earlier I wrote Schedule an SSIS Application with SSIS Framework Community Edition, a post that describes using SQL Server Agent to schedule the execution of an SSIS Application. In this post I’m going to cover adding SSIS Application metadata to SSIS Framework Community Edition. But first, I can hear some of you thinking, …

“What is an SSIS Application, Andy?”

I’m glad you asked! An SSIS Application is a collection of SSIS packages configured to execute in some order. Do you need an SSIS Framework to execute SSIS packages in some order? Nope. There are lots of ways to accomplish SSIS package execution management. Some of those ways, though, are the data integration equivalent of storing your enterprise data in Excel. ‘Nuff said.

The SSIS Framework uses three tables to organize SSIS Packages into SSIS Applications:

  1. custom.Applications
  2. custom.Packages
  3. custom.ApplicationPackages

The design of the custom.Applications table is fairly straightforward with two columns, ApplicationID and ApplicationName:

The design of the custom.Packages table is also pretty straightforward with SSIS Catalog Package storage metadata columns:

A Cardinal Rule

Let’s consider cardinality for a moment. If an SSIS Application is a collection of SSIS Packages, then the cardinality between applications and packages is one-to-many. But… one of the reasons for using an SSIS Framework is the SSIS Execute Package Task requires SSIS Packages exist in the SSIS Project (for SSIS Catalog execution using Project Deployment Model). Are there ways around this? Sure (please see my earlier comment about storing enterprise data in Excel…).

Wouldn’t it be awesome if you could store SSIS packages anywhere in the SSIS Catalog and then re-use them as part of several SSIS Applications? Yes. Yes it would. This is one advantage of using an SSIS Framework; it facilitates code re-use. Imagine you have an SSIS Package named ArchiveFile.dtsx that, well, archives flat files once their data has been loaded. You could add that package to every SSIS Project. If you go that route, what happens if you want to update ArchiveFile.dtsx? Or, God forbid, what do you do if you find a bug in ArchiveFile.dtsx? I reckon you open all those SSIS Projects, make the change, update source control, and then redeploy ArchiveFile.dtsx to all those SSIS Catalog projects (… and remember, you’d have to redeploy all packages in the projects prior to SSIS 2016…). Right?

Yea… no. Instead, you find (or build) a better mousetrap: an SSIS Framework.

Considering cardinality in a framework that facilitates code re-use, you realize the possibility that some SSIS Packages can be part of many SSIS Applications. The cardinality between SSIS Applications and SSIS Packages is really many-to-many. A bridge, or resolver, table is needed to manage the many-to-many relationship and I call this table custom.ApplicationPackages:


Each record represents an SSIS Package that runs as part of an SSIS Application. ApplicationPackageID is an identity column that manages this distinction. ApplicationID identifies the SSIS Application, PackageID identifies the SSIS Package, and ExecutionOrder defines the relative execution order of the SSIS Package in the SSIS Application. FailApplicationOnPackageFailure is an execution attribute. Technically ExecutionOrder is also an execution attribute. Why store this metadata at the ApplicationPackage? Let’s return to ArchiveFile.dtsx for a moment.

What if my data integration process is made up of one SSIS Package that loads data from a CSV file? The first SSIS Package in my SSIS Application will be the loader. The second SSIS Package in my SSIS Application will be ArchiveFile.dtsx.

But what if my data integration process contains several SSIS Packages? In that case, ArchiveFile.dtsx may execute 10th. Or 20th. Or even more than once. Remember, it’s the sameSSIS Package – ArchiveFile.dtsx. It’s just being called many times.

Why attribute FailApplicationOnPackageFailure to the ApplicationPackage? I may desire to continue SSIS Application execution if ArchiveFile.dtsx fails in SSIS Application A. But I may equally desire to stop the execution of SSIS Application B if ArchiveFile.dtsx fails. I hope this is making sense. Execution attributes are important.

Adding Framework Metadata

Adding SSIS Application metadata is straightforward because the design of the custom.Applications table is straightforward. When I write T-SQL for metadata operations, I like for the code to tell me what it’s doing. It’s awesome to know there was an error (well, not awesome exactly) or that the command completed successfully. But I’d like more information. Please. So I write T-SQL that is re-executable (idempotent). The T-SQL not only manages state, it informs me of state.

Adding an SSIS Application can be accomplished using the following T-SQL statement:


Why return the ApplicationID value? ApplicationID is one of the fields in ApplicationPackage.

Adding SSIS Package Framework metadata is similar:

Again, PackageID is required for ApplicationPackage metadata, which is where we next turn our attention:

These scripts will add SSIS Framework metadata to create (or return existing values for) an SSIS Application, SSIS Package, and map the SSIS Package into the SSIS Application. I can hear you thinking, …

“Great Andy. But What if I Already Have a Bunch of SSIS Packages Deployed to an SSIS Catalog?”

The T-SQL above lends itself to automation so I automated it. The script provided below is not as robust as I’d like for Production purposes but it demonstrates the principles and, well, it works. I won’t go into detail describing each line of code. I’m sure this can be accomplished differently and I’m pretty sure it can be done better. Suffice it to say this T-SQL script loads SSIS Framework Community Edition metadata for an SSIS Project deployed to an SSIS Catalog. You set the name of the SSIS Application by setting the @frameworkApplicationName parameter. Supply the name of the SSIS Catalog Folder (@catalogFolderName) and Project (@catalogProjectName). Unless the order of execution doesn’t matter, you will likely have to update the ExecutionOrder values once the script completes.

Please remember this T-SQL starts a transaction but does not commit it. Commit and Rollback statements are at the end of the script, commented out so you can determine which to execute based on the results of the test query.

DISCLAIMER: This script will add SSIS Packages metadata to the SSIS Framework Community Edition in a non-deterministic order. Before executing the SSIS Application, please review the results returned by the final SELECT query in this script and update the ExecutionOrder values as needed to support your enterprise data integration requirements.


Please consider this a beta version of this T-SQL script for loading metadata for deployed SSIS Projects into SSIS Framework Community Edition. As always, I welcome your feedback.



You might like working with Enterprise Data & Analytics because we grok SSIS Frameworks.

Learn More:

Expert SSIS Training Online with Brent Ozar Unlimited!
IESSIS1: Immersion Event on Learning SQL Server Integration Services – 2-6 Oct 2017, Chicago
What is the DILM Suite? 2. SSIS Catalog Compare
What is the DILM Suite? 1. SSIS Framework Community Edition
Adding an SSIS Application to SSIS Framework Community Edition
Schedule an SSIS Application with SSIS Framework Community Edition
The Heart of SSIS Framework Community Edition-Parent.dtsx
SSIS Framework Community Edition Updates
My Latest Book – Building Custom Tasks for SQL Server Integration Services – Is Now Available!

Enterprise SSIS, Biml, and DILM (recording)
Designing an SSIS Framework (recording)
Biml in the Enterprise Data Integration Lifecycle (recording)
Password: BimlRocks