The Heart of SSIS Framework Community Edition-Parent.dtsx

Note: This post was originally posted at SQLBlog.

I’m writing about SSIS Framework Community Edition because lots of people are using this free, open-source utility to execute collections of SSIS packages 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 and Adding an SSIS Application to SSIS Framework Community Edition, a post describing how to add metadata to your SSIS Framework for SSIS Packages already deployed in your enterprise. I even included a free T-SQL script to help! (You are welcome.)

This post is about the engine at the heart of SSIS Framework Community Edition – the Parent.dtsx SSIS package.

The Little Engine That Could (and Still Can!)

As I mentioned in an earlier post, there are a handful of large enterprises (still) running early versions of my Framework – versions that are not integrated with the SSIS Catalog (because the SSIS Catalog didn’t exist back then) – and with less functionality.

Here’s a composite screenshot of the SSIS Framework Community Edition Parent.dtsx package:

This is it, folks. That’s all there is. There ain’t no more! Parent.dtsx consists of a Package Parameter named ApplicationName, a package-scoped ADO.Net Connection Manager, two Execute SQL Tasks, a Script Task, a Sequence Container, a Foreach Loop Container, and a handful of SSIS Variables.

“How Does it Work, Andy?”

I’m glad you asked! The Execute SQL Task named SQL Get Framework Packages executes the following T-SQL query against the SSISDB (SSIS Catalog) database:

Select p.PackageName
, p.ProjectName
, p.FolderName
, ap.FailApplicationOnPackageFailure
From custom.ApplicationPackages ap
Join custom.Packages p
On p.PackageID = ap.PackageID
Join custom.Applications a
On a.ApplicationID = ap.ApplicationID
Where a.ApplicationName = @ApplicationName
Order By ap.ExecutionOrder

If we execute this query in SQL Server Management Studio (SSMS) for the “Framework Test” SSIS Application, we see the following results returned:

Returning to the SQL Get Framework Packages Execute SQL Task in Parent.dtsx, the @ApplicationName parameter is mapped to the $Package::ApplicationName SSIS Package Parameter:

The Full Result Set is sent to the SSIS Variable (Object data type) named User::ApplicationPackages:

The FOREACH Application Package Foreach Loop Container uses a Foreach ADO Enumerator to shred the contents of the User::ApplicationPackages SSIS Variable:

As the Foreach Loop Container enumerates, it points to each row – one at a time – in the first (and only) table in the Tables collection of the ADO.Net Dataset. Values from each column in the “current” row are read into SSIS Variables:

Comparing this image to the results of the query, the Foreach Loop Container is first read the value “Child1.dtsx” into the User::FrameworkPackage SSIS variable (Index 0). “FrameworkTest1” is read into User::FrameworkProject (Index 1), “Test” is read into User::FrameworkFolder (Index 2), and 1 is read into User::FailApplicationOnPackageFailure (Index 3).

Execution

An Execute SQL Task named SQL Execute Child Package is the first executable inside the Foreach Loop Container:

The SQL Execute Child Package Execute SQL Task executes a stored procedure in the SSISDB (SSIS Catalog) database named custom.execute_catalog_package:

If you use an ADO.Net connection type, you can set the IsQueryStoredProcedure property to True. This removes the need to call the stored procedure with Exec (or Execute). You also do not need to list the parameters after the name of the stored procedure, you simply add them – by name – on the Parameters page.

An Aside: “How I Did It” by Victor Andy Frankenstein

The custom.execute_catalog_package stored procedure is part of the open-source, free SSIS Framework Community Edition download available at DILM Suite. Here’s a screenshot of some of the T-SQL:

The custom.execute_catalog_package stored procedure was initially built from T-SQL generated when one scripts the execution of an SSIS Package in the SSIS Catalog. If you expand the SSMS Object Explorer’s Integration Services Catalogs node until you locate an SSIS Package, you can right-click the package and then click Execute:

When the Execute Package window displays, click the Script button to generate a collection of stored procedure calls:

The Script button opens a new query window (by default) and displays T-SQL scripts that you can execute to start the execution of an SSIS Package in the SSIS Catalog:

Does this code look familiar? It should. This T-SQL is the basis for the T-SQL in the custom.execute_catalog_package stored procedure.

Back to the Fact I’m the Mack and I Know That (Not Really… I Just Wanted a Segue and an Excuse to Link to That Video…)

The SQL Execute Child Package Execute SQL Task starts the SSIS Package by passing the values from the User::FrameworkPackage, User::FrameworkProject, and User::FrameworkFolder SSIS variables to the custom.execute_catalog_package

Remember, these SSIS variables were three of the four shredded from the User::ApplicationPackages (object data type) SSIS variable by the FOREACH ApplicationPackage Foreach Loop Container.

“It’s Your Fault! No! It’s Your Fault!”

The fourth SSIS variable is User::FailApplicationOnPackageFailure, which is not passed to the custom.execute_catalog_package stored procedure. Instead, FailApplicationOnPackageFailure is used if the custom.execute_catalog_package returns a failure result, which will happen if the SSIS Package fails execution. When that happens the Failure Precedence Constraint between the SQL Execute Child Package Execute SQL Task and the SCR Evaluate Package Failure setting Script Task evaluates as True, and the SCR Evaluate Package Failure setting Script Task executes:

SCR Evaluate Package Failure setting evaluates the FailApplicationOnPackageFailure SSIS Variable:

If FailApplicationOnPackageFailure is set (1, or True), SCR Evaluate Package Failure setting raises an error. If FailApplicationOnPackageFailure is not set (0, or False), SCR Evaluate Package Failure setting executes successfully:

In this way, Parent.dtsx manages fault tolerance. I can hear you thinking, …

“So How Does Parent.dtsx Continue Executing When There’s a ‘Tolerable’ Error, Andy?”

You don’t miss much, I’ll give you that. First we have to stop the Foreach Loop Container from failing when an error occurs. We do that by setting the MaximumErrorCount property to 0. To prevent the package from failing, we need to duplicate this setting for the Sequence Container and for Parent.dtsx:

So how do we get Parent.dtsx to fail when the FailApplicationOnPackageFailure is set? We set the FailPackageOnFailure property to True for the SCR Evaluate Package Failure setting Script Task:

When the SCR Evaluate Package Failure setting Script Task returns a failure result, Parent.dtsx fails. If the SSIS Package executed by the SQL Execute Child Package Execute SQL Task fails (returning a failure result to the Execute SQL Task) and FailApplicationOnPackageFailure is not set (False), SCR Evaluate Package Failure setting succeeds and doesn’t fail Parent.dtsx. So Parent.dtsx continues executing. Cool? Cool.

Conclusion

Parent.dtsx looks simple but, as we’ve learned in this post, this simple SSIS Package is an elegant solution for executing collections of SSIS Packages. Via Parent.dtsx, SSIS Framework Community Edition supports metadata-driven SSIS Package ordered execution and includes configurable package-level fault tolerance. Enterprise Data & Analytics builds SSIS Frameworks Commercial and Enterprise Editions, as well as custom SSIS Frameworks to support enterprise data integration. Contact us. We can help.

:{>

—

You might like working with Enterprise Data & Analytics because we think enterprise SSIS Frameworks are groovy.

—

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!