A few weeks ago I published a blog post titled Why I Built DILM Suite, by Andy Leonard – an excerpt from the book Data Integration Life Cycle Management with SSIS. Last week I published Why I Built SSIS Catalog Compare, by Andy Leonard, another excerpt from the book. This post is another excerpt of the same book. Enjoy!
A best practice in SSIS development is to build small, unit-of-work SSIS packages. There are several reasons for this:
- SSIS is software development and a best practice with software development is separation of concerns. Separation of concerns is primarily achieved by decoupling. One way to decouple SSIS is to build small, single-function SSIS packages.
- If an SSIS package contains seven Data Flow Tasks and the design of a source table changes and breaks one Data Flow Task, all tasks in the SSIS Package should be tested. Fewer Data Flow Tasks means less and quicker testing.
- If all SSIS packages contain the minimum number of Data Flow Tasks (optimally one) and a package execution fails in the middle of the night, on-call support has a pretty good idea where to begin troubleshooting.
“There’s No Free Lunch”
While these are good and valid reasons to build SSIS solutions with several smaller SSIS packages, following this advice creates new issues. One issue: You now have a bunch of SSIS packages that require executing in some order. What’s a data integration developer to do?
8.1 SSIS Framework Community Edition
Consider the SSIS Framework Community Edition, a free and open-source solution available at DILM Suite. SSIS Framework Community Edition allows the execution of one or more SSIS packages – in a specified execution order – by executing a single stored procedure and passing it a single argument. For example, I can execute a test Framework Application with the following Transact-SQL statement:
exec custom.execute_catalog_parent_package @application_name = 'Framework Test '
Continuing my theme of “there’s no free lunch,” SSIS execution frameworks greatly simplify execution commands like the one listed above but they create another issue: the need to manage a lot of metadata. SSIS Framework Community Edition relies on metadata to build a Framework Application – mentioned earlier. A Framework Application is a collection of SSIS packages configured to execute in a specified order. If you build idempotent (re-executable) Transact-SQL that includes print statements (to inform you of what the T-SQL is doing) and use any kind of formatting, you’re looking at 30-40 lines of Transact-SQL per SSIS package.
That’s a lot of T-SQL.
8.1.1 Help for SSIS Catalog Projects Already Deployed
Perhaps you are reading this and thinking, “That’s awesome, but I have a bajillion SSIS packages already deployed to my SSIS Catalog. What about them?” I wrote a blog post titled Adding an SSIS Application to SSIS Framework Community Edition. I included a script at the end of that post that uses three parameters – Framework Application Name, Catalog Folder Name, and Catalog Project Name – and from those three pieces of metadata loads the metadata for a new Framework Application into SSIS Framework Community Edition’s metadata tables, as shown in Figure 8-1:
Figure 8-1. Building a Framework Application from an SSIS Catalog Project
The script reads SSIS Catalog Project metadata shown in Figure 8-2 and loads the Framework Application metadata into SSIS Framework Community Edition metadata tables in a few seconds:
Figure 8-2. The SSIS Catalog Project
The Framework Application named “Load AdventureWorks2014 Stage” which contains 71 SSIS packages can now be executed with the following Transact-SQL statement:
exec custom.execute_catalog_parent_package @application_name = 'Load AdventureWorks2014 Stage'
8.2 Viewing SSIS Catalog Reports
We can view the executions of these 71 SSIS packages using the Catalog Reporting solution built into SSMS. To view all SSIS package executions, right-click the SSMS Object Explorer Integration Services Catalogs node’s SSISDB node, hover over Reports, hover over Standard Reports, and click All Executions as shown in Figure 8-3:
Figure 8-3. Opening the Built-In SSIS Catalog Reports
The All Executions report displays and surfaces SSIS package execution logs as shown in Figure 8-4:
Figure 8-4. The SSIS Catalog All Executions Report
To summarize, we supplied three pieces of metadata to a Transact-SQL script that built a Framework Application containing 71 SSIS packages, and then we executed those 71 SSIS packages by starting a single stored procedure and passing it one parameter value.
8.3 Viewing SSIS Framework Community Edition Metadata
I mentioned earlier there’s quite a bit of metadata required for the SSIS Framework Community Edition. The script we used earlier is a nice piece of automation for entering SSIS Framework Community Edition metadata, but what happens when we want to view the Framework Applications already stored?
Framework Browser
Enter Framework Browser, another free utility from DILM Suite that you can download from DILM Suite – shown in Figure 8-5:
Figure 8-5. The Framework Application Load AdventureWorks2014 Stage
A Framework Application is a collection of SSIS packages – called Application Packages in the Framework – that execute in a specified order. Framework Browser lists Application Packages in the order they execute.
If we expand the Application Properties virtual folder, we see Framework Application metadata. Expand the Application Package node and the Application Package Properties node to surface Application Package metadata as shown in Figure 8-6:
Figure 8-6. Surfacing Application and Application Package Framework Metadata
Framework Browser is another free utility from DILM Suite.
Comments