In this post I will demonstrate advanced options for executing SSIS packages in the SSIS Catalog. I’ll also demonstrate SSIS Catalog Browser, SSIS Framework Community Edition, Framework Browser, and Catalog Reports – free utilities and solutions from DILM Suite that may help as you implement your enterprise Data Integration Lifecycle Management (DILM).
The Problem I am Trying to Solve
I am staging data from flat files using SSIS packages in two SSIS Projects. Here are the projects containing the packages as viewed in SSIS Catalog Browser:
I want to execute these packages from the SSIS Catalog in the following order:
ArchiveFile.dtsx (to archive the Widgets flat file)
ArchiveFile.dtsx (to archive the Sales flat file)
I’ve followed an SSIS development best practice and developed several SSIS projects containing function-focused, unit-of-work SSIS packages. I cannot use the Execute Package Task because it only allows me to execute SSIS packages contained within the same SSIS project:
What to do? Well, I could go all custom task on you…
… but I am getting way ahead of myself. I’ll write more later about the ALC Execute Catalog Package Task. Promise.
Three (Well, Four) SQLAgent Job Steps
I can create a SQLAgent job with
three four job steps (I want to execute the ArchiveFile.dtsx package twice, once per flat file):
One may schedule a job step for each SSIS package one wishes to execute. I’ve seen SQLAgent jobs that execute other SQLAgent jobs which in turn execute SSIS packages from the SSIS Catalog. There’s nothing wrong with this approach and there are benefits to using SQLAgent as an SSIS workflow manager.
“But…” (you saw this “but” coming, didn’t you?)
While I like using SQLAgent as a scheduler, I prefer to only use it as a scheduler. I prefer to use (or build) a data integration workflow management solution to manage data integration workflow.
SSIS Framework Community Edition
Kent Bradshaw and I work together at Enterprise Data & Analytics. We built the SSIS Framework to manage data integration workflow. Our frameworks are available in three flavors: Community Edition, Commercial Edition, and Enterprise Edition. The Community Edition is not only free, it’s open source! The documentation (SSIS Framework Community Edition Documentation and User Guide.docx) is a primer on designing your very own SSIS Framework.
Like all editions of our frameworks, SSIS Framework Community Edition is integrated into the SSIS Catalog. Why? We want our frameworks to participate in your Data Integration Lifecycle, and we believe the SSIS Catalog is a vital component of enterprise DILM (Data Integration Lifecycle Management).
Many enterprise data integration platforms include built-in framework functionality. Why? Because framework functionality is necessary for enterprise DILM (Data Integration Lifecycle Management). The SSIS Catalog is a framework but it lacks some of the functionality found in competing data integration platforms. You can implement the missing functionality in SSIS. The SSIS Framework Community Edition implements some of the missing functionality.
How can SSIS Framework Community Edition help?
It may help to understand some fundamentals of our framework first. Our Framework contains three entities: Applications, Packages, and Application Packages. The core object is the Application Package. The metadata contained in the custom.ApplicationPackages table isn’t terribly exciting:
An Application Package represents a Framework Package that will execute as part of a Framework Application, as you can glean from the metadata in the screenshot above. The last four Application Packages shown represent four packages that execute as part of Application ID 2. The Package IDs for these four packages are 4, 5, and 6. “But wait, Andy. You wrote four packages. Why are there only three PackageID values?” That is an excellent question. The answer is: PackageID 6 appears twice.
The Framework Package table is straightforward and simple. It contains metadata used to identify the Package in the SSIS Catalog. All we need is the Catalog “path” to the SSIS Package. The Catalog path is a four-part hierarchy: Catalog\Folder\Project\Package. For now, there can be only one SSIS Catalog per instance of SQL Server and it is always named “SSISDB.” So we don’t store that value in the SSIS Framework Community Edition:
The last three packages listed are PackageIDs 4, 5, and 6: LoadWidgetsFlatFile.dtsx, LoadSalesFlatFile.dtsx, and ArchiveFile.dtsx. These are the packages that are part of our Framework Application. You can think of a Framework Application as a collection of Framework Packages configured to execute in a specified order:
The “Stage EDW Data” Framework Application is identified by ApplicationID 2. If you recall, ApplicationID 2 is mapped to PackageIDs 4, 5, and 6 (LoadWidgetsFlatFile.dtsx, LoadSalesFlatFile.dtsx, and ArchiveFile.dtsx) in the ApplicationPackages table shown above.
The cardinality between Framework Applications and Framework Packages is many-to-many. We see an Application can contain many Packages. The less-obvious part of the relationship is represented in this example: a single Package can participate in multiple Applications or even in the same Application more than once. Hence the need for a table that resolves this many-to-many relationship. I hope this helps explain why Application Package is the core object of our SSIS Frameworks.
The Parent.dtsx SSIS Package
In SSIS Framework Community Edition (and the other editions of our SSIS Framework), an SSIS package named Parent.dtsx serves as the workflow engine:
Community Edition’s Parent.dtsx package has a single Package Parameter named ApplicationName:
The ApplicationName parameter is used in the first Execute SQL Task – named “SQL Get Framework Packages” – which selects the Application Packages based on ApplicationName and ordered by the ExecutionOrder attribute stored in the Application Packages table:
The query, executed to select the Application Packages contained in the “Stage EDW Data” Framework Application, returns these results:
The “SQL Get Framework Packages” Execute SQL Task stores this full resultset to an SSIS object data-type variable named User::ApplicationPackages:
A Foreach Loop named “FOREACH Application Package” uses a Foreach ADO Enumerator to shred the contents of the User::ApplicationPackages object variable:
The Foreach ADO Enumerator “points” at each row in the resultset in the order the rows are retrieved. It copies the values from each field into other SSIS variables, as shown here:
Inside the “FOREACH Application Package” Foreach Loop Container, an Execute SQL Task named “SQL Execute Child Package” calls a stored procedure named custom.execute_catalog_package (we’ll take a closer look at this stored procedure in a bit):
Custom.execute_catalog_package requires three parameters: @package_name, @project_name, and @folder_name. They are supplied by the “SQL Execute Child Package” Execute SQL Task from the SSIS variables mapped in the “FOREACH Application Package” Foreach Loop Container:
As a result of the “FOREACH Application Package” Foreach Loop Container variables mappings, the “SQL Execute Child Package” Execute SQL Task will execute, calling the custom.execute_catalog_package stored procedure and passing it the values on each row, in order, of the resultset returned by the “SQL Get Framework Packages” Executequery.
There’s more, but your head is hurting enough for one blog post.
A Test Execution
Executing the Parent.dtsx package in SSDT succeeds:
A quick glance at Catalog Reports confirms all four Application Packages executed:
There’s one last thing. You can view the contents of the SSIS Framework Community Edition using Framework Browser, another free utility from DILM Suite.
When you first open Framework Browser you may be prompted to for “Defaults.” If so, click File—>Open on the Defaults form and navigate to a file named CommunityEdition.json:
When you open CommunityEdition.json, the Defaults form will appear similar to this:
Click the Save button to return to the Framework Browser main form.
In the Catalog Instance textbox, enter the name of the SQL Server instance that hosts your SSIS Framework Community Edition and click the Connect button. You Framework metadata should load and appear similar to that shown in this screenshot:
Framework Browser surfaces SSIS Framework Community Edition metadata.
In this post I demonstrated advanced options for executing SSIS packages in the SSIS Catalog. I demonstrated SSIS Catalog Browser, SSIS Framework Community Edition, Framework Browser, and Catalog Reports – free utilities and solutions that make up the DILM Suite that may help you as you implement your enterprise Data Integration Lifecycle Management (DILM). I even snuck in a screenshot of a custom SSIS task – the Execute Catalog Package task – of which I’ll write more later.
You might like working with Enterprise Data & Analytics because we like helping teams learn more about the SSIS Catalog.
Advanced SSIS Execution
SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy
SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring
Previous Posts in this Series:
An Example of Data Integration Lifecycle Management with SSIS, Part 0
An Example of Data Integration Lifecycle Management with SSIS, Part 1
An Example of Data Integration Lifecycle Management with SSIS, Part 2
An Example of Data Integration Lifecycle Management with SSIS, Part 3
SSIS Lifecycle Management (free recording, registration required)
IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago
IESSIS2: Immersion Event on Advanced Integration Services – Oct 2017, Chicago
From Zero to Biml - 19-22 Jun 2017, London