THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast.

The Heart of SSIS Framework Community Edition-Parent.dtsx

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:

Par0

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:

Par1a

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:

Par1

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

Par2

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

Par3

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:

Par4

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:

Par6

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

Par7

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

Par12

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:

Par5

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:

Par9

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

Par10

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:

Par11

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

Par8

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:

Par13

SCR Evaluate Package Failure setting evaluates the FailApplicationOnPackageFailure SSIS Variable:

Par15

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:

Par14

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:

ParMaxErrorCount

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:

Par16

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:

Adding an SSIS Application to SSIS Framework Community Edition
Schedule an SSIS Application with SSIS Framework Community Edition
Designing an SSIS Framework (recording)
Biml in the Enterprise Data Integration Lifecycle (recording)
From Zero to Biml - 19-22 Jun 2017, London 
IESSIS1: Immersion Event on Learning SQL Server Integration Services – Oct 2017, Chicago

Published Tuesday, May 09, 2017 8:30 PM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement