DILM Suite + Azure Data Factory Integration Runtime

DILM Suite was designed to support enterprise data engineering / data integration with SSIS. But the solutions, utilities, and tools work well with Azure Data Factory Integration Runtime (ADF IR, or “SSIS in the cloud”).

As I mentioned in my post titled The Cloud Costs Money, leaving ADF Integration Runtime running can get pricey – and fast.

Most DILM Suite Functions Do Not Require ADF Integration Runtime to be Running

That’s right. Since DILM Suite tools and utilities connect directly to the database, they do not require Azure Data Factory Integration Runtime to be in a running state for most operations.

Here, for example, I’ve connected Framework Browser to an instance of SSIS Framework Community Edition – deployed to the ADF Integration Runtime as demonstrated in the latest Summer-O’-ADF webinar, Designing a Custom ADF SSIS Execution Framework – doesn’t have to be in a Running state to allow interaction with Framework Browser and Catalog Browser (click to enlarge):

That’s handy information right there. And it can save you money.


Updates to Three DILM Suite Applications

It’s been a busy couple weeks here at Enterprise Data & Analytics in Farmville. I just published updates to not one, not two, but three – yes, three! – applications that are part of the Data Integration Lifecycle Management Suite, or DILM Suite.

SSIS Framework Community Edition

To prepare for Thursday’s (12 Jul 2018) webinar titled Designing a Custom ADF SSIS Execution Framework, I updated the documentation for SSIS Framework Community Edition. Why did I update the documentation? Because I want folks to know they can use SSIS Framework Community Edition in the cloud! That’s right, SSIS Framework Community Edition – still free, still open source – works with the SSIS Catalog behind Azure Data Factory Integration Runtime.

SSIS Framework Browser

Also in preparation for Thursday’s webinar, I updated SSIS Framework Browser! Version (beta) remains free and now also works with SSIS Framework Community Edition implemented in Azure Data Factory Integration Runtime. Use Framework Browser to view metadata for SSIS Applications and Application Packages stored in the Framework.

SSIS Catalog Browser

Finally, I updated the login experience for those using SSIS Catalog Browser to connect to Azure Data Factory Integration Runtime. As with all Catalog Browser updates, I’ve applied the same updates to the user experience in SSIS Catalog Compare. Which reminds me…

SSIS Catalog Compare

On Thursday 19 Jul 2018 at noon EDT, I’m delivering another free webinar titled Use SSIS Catalog Compare to Lift and Shift SSIS to ADF! In this webinar, I will be demonstrating some of the features in version 3, which is approaching Preview.

I hope you will join me for these upcoming webinars and that you’ll take the free DILM Suite products for a test drive!

The Summer-O’-ADF Series Continues!


Free Webinar – Designing a Custom ADF SSIS Execution Framework

Folks, I’m not going to even pretend to be cool and collected about this webinar about designing an ADF SSIS Execution Framework. I’ve been waiting for this one for a loooooooong time! The next (free!) webinar in the series I’m calling the Summer-O’-ADF is Designing a Custom ADF SSIS Execution Framework.

It’s at noon EDT Thursday 12 Jul 2018.

Why Am I So Excited?

I’ve been designing data engineering frameworks since the days of DTS (Data Transformation Services). Enterprise Data & Analytics (EDNA) implements SSIS Frameworks for enterprises. We even give away a free version of a framework at DILM Suite (DILM == Data Integration Lifecycle Management). It’s called SSIS Framework Community Edition and it’s not only free, it’s open source.

I wrote about SSIS Framework Community Edition and Data Integration Lifecycle Management in my latest book: Data Integration Lifecycle Management with SSIS.

I’m excited about this webinar because it combines a long-held passion – DevOps for SSIS – with a new passion – Azure Data Factory (ADF)! I cannot wait to show you how!

If you haven’t seen my webinars titled The Azure Data Factory Controller Design Pattern and ADF Controller Design Pattern with the SSIS Integration Runtime (also part of the Summer-O’-ADF webinar series), you may want to check them out first (registration required).

Register today!


Two Recordings Available: SSIS Catalog and SSIS Lifecycle

I recently delivered two hour-long webinars: Introduction to Configuring SSIS in the SSIS Catalog and Introduction to the SSIS Lifecycle. These two webinars can be considered Part 1 and Part 2 for managing enterprise data engineering with SSIS – using the SSIS Catalog and utilities I built as part of the DILM Suite (DILM == Data Integration Lifecycle Management).

The recordings are now available and you can access them by clicking the links above.

What’s Covered

In Introduction to Configuring SSIS in the SSIS Catalog I cover deploying SSIS projects to the SSIS Catalog, configuring literal overrides, and using references to environments to externalize values via reference mappings.

In Introduction to the SSIS Lifecycle I demonstrate gaps in SSIS Catalog lifecycle management and demonstrate utilities I built to close said gaps – utilities that are available at DILM Suite. Most solutions and utilities at DILM Suite are free and some are even open source. Some – like SSIS Catalog Compare – are not free.

  1. If your enterprise is using SSIS 2012+, you should consider the SSIS Catalog.
  2. If you are going to use the SSIS Catalog, you should consider using environments and references for configurations management.
  3. If you’re going to use environments and references for configurations management, you will hit a wall when it’s time to promote environments and references to the next tier in your lifecycle.

In these webinars, I show you the wall.
And then I show you the stairs I built over that wall.

Check out the webinars (registration required):



Why I Built SSIS Framework Community Edition, by Andy Leonard

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:

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:

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.

Presenting DevOps for SSIS 08 Feb 2018

I am honored to present DevOps for SSIS (or Data Integration Lifecycle Management) for the PASS DevOps Virtual Chapter 08 Feb 2018.

DevOps for SSIS (or Data Integration Lifecycle Management) How does SSIS participate in enterprise DevOps? Sharing topics from his latest book (Data Integration Life Cycle Management with SSIS), author and presenter Andy Leonard shares hard won advice from the trenches. Data Integration Lifecycle Management (DILM) is the key, and SSIS design and development must be approached with DILM in mind. Andy shares one way to accomplish enterprise DILM along with patterns, best practices, and a few utilities he’s built to help achieve DevOps with SSIS.

Register today!