SSIS Frameworks, An Odyssey
Way back in 2007, I was subcontracted to use SSIS to load data from a couple dozen companies that were now owned by one company into SAP. If memory serves, this was the first project of its size - arguably an enterprise project - for which I used SSIS. I was barely into my career as a data person, having progressed from hobbyist to professional software developer (and an MCSD, Microsoft Certified Solutions Developer) the prior couple decades.
Part of software development is automation, testing, and design patterns, and I was just getting into them all in a major way when I jumped the fence to data work. I remember thinking, “We could sure use some of that software stuff over here in data integration.”
So I built a few tables in a small database, wrote a “parent” SSIS package, and was off to the races.
I was on that gig for about six months. Late in the project cycle, I discovered work written by Allan Mitchell, a data engineer and software developer who co-authored Professional SQL Server 2005 Integration Services (along with myself and others), that solved the same problem; namely, metadata-driven execution orchestration of a collection of SSIS packages. I confess: It felt good to find Allan’s work. I remember thinking, “I thought of a solution similar to what The Allan Mitchell built!” That thought made me feel smart (and I need all the help I can get to feel smart!).
Later, the data architect for the company where I consulted on the SAP project delivered a presentation at the Richmond SQL Server Users Group. He liked the SSIS Framework and mentioned both it and me during the presentation. In attendance were a couple data engineers from the Federal Division of Unisys Corporation. Unisys had recently looked into making the switch from Informatica to SSIS to manage data integration for state level MMIS’s (Medicaid Management Information Systems). As it turned out, Informatica shipped with execution orchestration functionality right out of the box. The attending Unisys engineers reached out to me and, long-story-short, I was hired by Unisys - in part to build an SSIS framework tailored for MMIS needs.
It wasn’t a lot of work because the original version was generic, simple, and elegant (wears out 3 sets of elbows patting myself on the back).
Since I showed up at Unisys with my version of an SSIS framework, I continued selling it as a solution to consulting clients after I left and returned to consulting. Keep in mind that, until that point, I’d experienced two spectacular successes in a row related to frameworks. The successes didn’t stop, but the streak didn’t last either. I would love to characterize the failures in flowery language like, “I was ahead of my time” or some such. But the truth is more brutal. My failures were mine alone. They fell into two basic categories:
Misapplying the solution - where I thought a framework would solve all the data integration problems; and
Miscommunicating the complexity of a solution that made perfect sense… according to the pictures in my head.
My friend and brother from another mother, Kevin Hazzard, suggested a gathering of data integration consultants. “Andy,” Kevin mused, “this framework needs a user interface.”
Kevin wasn’t wrong. Long-story-short (#2), that gathering got sidetracked.
I became more guarded in offering SSIS frameworks to customers. I checked first for indications:
Could a candidate enterprise really use solutions to the problems an execution orchestration framework could solve - especially the way I’d solved them?
I got better at qualifying candidate enterprises, searching specifically for in-house experience and expertise with both automation and data integration; and
I (eventually) developed a beta-plus-workshop approach to implementing my SSIS framework.
Together, these changes helped, but Kevin remained as right as he ever was: the solution was missing a user interface. I decided to tackle the project in bite-sized chunks.
That’s how DILM Suite started.
Data Integration Lifecycle Management Suite
I started writing the foundation for the SSIS Framework Manager UI when I founded the idea for Enterprise Data & Analytics (EDNA) ten years ago on 15 Sep 2015.
After leaving my previous venture, I immediately started writing what would become SSIS Catalog Compare. SSIS Catalog Compare (SCC) “supports SSIS project configuration, deployment, lift-and-shift, on-premises migration, code promotion, and comparison between SSIS Catalogs, versions 2012 – Azure Data Factory SSIS Integration Runtime (Azure-SSIS).”
Since SCC treats SSIS Catalog contents as code, it’s proven to support DevOps and DevSecOps, like many other Configuration-as-Code solutions. SCC can script all the contents of an SSIS Catalog into a single file. Drop that file into a local git repo, sync to the remote repo, and voila. Also, DILM Deployment Utility (more on this particular utility later…) facilitates deploying all those SCC-scripted SSIS Catalog contents to a new (or alternate) SQL Server instance in a few clicks.
Writing all the functionality for SSIS Catalog Compare turned out to be a bigger “bite” than I originally thought, especially when I encountered some previously-unforeseen “features” in the .Net Framework’s support for the Integration Services Catalogs node in SQL Server Management Studio (SSMS) and SSIS management functionality.
Long-story-short (#3), I realized (with some help and guidance from Kevin Hazzard) that in order to build the software to work as I wanted, I needed to build my own SSIS Catalog base class.
And so I did that.
To help tackle this large-ish effort, I split the SSIS Catalog Compare interface into a single-treeview version for test simplification purposes. Later, I decided to release this version as SSIS Catalog Browser.
SSIS Catalog Browser “presents a rich, unified view of the SSIS projects, packages, and configurations metadata deployed to enterprise SSIS Catalog instances.” I was able to build a functioning SSIS Catalog base class that I (creatively) named CatalogBase. Building CatalogBase took a while to get right. A funny thing happened along the way, though: I learned a lot about how the SSIS Catalog was designed. Most of my learning was gleaned from the design of the SSISDB database, and much of that learning was informed by capturing SQL statements and queries from traces during SSIS package executions and then using the “Debug” button* in SSMS to step through the execution of SSISDB stored procedures.
Once I felt CatalogBase was stable, I cut the code back into SSIS Catalog Compare and completed a release of SCC.
I was then free to start development in earnest on SSIS Framework Manager.
A Very Important Person in this Story
About a year after I left Unisys, one of the engineers from the SSIS team, Kent Bradshaw, joined the consultancy I cofounded. I’d always enjoyed working with Kent and our friendship only grew as we tackled consulting gigs together.
Kent remains one of my best friends.
When I founded EDNA, Kent joined me immediately. He and I have been working together since that time. Together, we have continued to build DILM Suite products.
SSIS Framework Manager
As Kent and I started coding SSIS Framework Manager, we realized a couple things:
Supporting an application that manages stored metadata requires a lot of database-level support; and
It was getting crowded in the SSISDB database.
The SSIS Catalog did not come into existence until 2012 with the release of SQL Server 2012 and SSIS 2012. If you recall, I built my original SSIS Framework back in 2007. My original framework, and the version I built at Unisys, were not SSIS Catalog-integrated because, well, there was no SSIS Catalog.
Kent and I worked together to build the first SSIS Catalog-integrated version of the SSIS Framework between Christmas 2012 and New Year’s Day 2013. It was a bit of a slog, but we pulled together a minimally-viable product (MVP) before we really knew what the term meant (I didn’t listen to the audio book for The Lean Startup until later).
Kent and I had started pair-programming DILM Suite utilities back in the SSIS Catalog Compare development days.
We doubled-down on SSIS Framework Manager.
In the middle of the first SSIS Framework Manager development cycle (yes, this implies exactly what you’re thinking right now…), we made changes to the SSIS Catalog-integrated version of the SSIS Framework. We left the “file-system package storage” version of the framework - the one I’d originally built and then modified at Unisys - as it was.
I made mistakes in both the architecture and design of the framework changes.
As I describe my tunnel-vision at times, I “fell into a hole.”
Fortunately, some of our SSIS Framework customers requested additional features. This gave us an excuse to put the development of SSIS Framework Manager on hold while we focused on updates to the very framework said Manager would, well, manage.
I hope you’re sitting down. If not, I suggest you sit down before reading this next part.
Seated?
Good, I will proceed.
Upgrading the SSIS Framework experienced scope creep.
I know! I can hardly believe it myself and I was right there, developing it in front of God and everybody. Well, not everybody. Kent, though. I was developing it right there in front of God and Kent.
One of the decisions we made was to decouple the SSIS Framework metadata database from SSISDB. I previously justified adding metadata tables and management artifacts to SSISDB by separating them into new schemas. A potential client pointed out that their enterprise data team considered SSISDB a system database and would brook no compromise on the matter.
They were right.
I was wrong.
Technically, this change wasn’t scope creep. Kent and I booted the scope over our original boundary on purpose.
When we returned to SSIS Framework Manager development (Part 2, The Search for More Money - Spaceballs 2 has been announced, by the way!), we reconsidered the design. We even considered starting over from scratch, but then refactored instead.
This brings us to today.
Today
We’re testing SSIS Framework Manager like crazy.
If you’ve ever built software, you know that testing often results in more coding - either fixing things that don’t work or adding things you come to realize need to be a part of the final release.
The tests are going well enough for me to feel comfortable releasing a video showing me building a (very) basic SSIS Framework application using SSIS Framework Manager:
The treeview in the video on the right is, in fact, an SSIS Catalog viewer. CatalogBase holds the model. The view is a treeview shared among SSIS Catalog Compare, SSIS Catalog Browser, and now SSIS Framework Manager. You may note from viewing the earlier images and this video that the SSIS Catalog displays differently in SSIS Framework Manager. That’s intentional, but its still the same base class.
“What’s Next, Andy?”
That’s an excellent question! I’m glad you asked.
Kent and I are having too much fun to quit now! That much is certain.
We have ideas.
Lord willing, those ideas will come to fruition.
We’ll share more at that time.
But first, we need to get SSIS Framework Manager launched!
*Sadly, the Debug button was removed from SSMS in version 18. I’ve lobbied to have the button + functionality re-added, without success (to date). My current plan is to continue bugging the good people at Microsoft who maintain and develop SSMS until I die or they capitulate just to shut me up, whichever comes first.
Wish me luck.
:{>
I am going to have to take this meme and use it somewhere at some point