The following is Chapter 6: Catalog Browser from my latest book titled Data Integration Life Cycle Management with SSIS: A Short Introduction by Example:
I was honored to be a Microsoft SQL Server MVP for five years (2007-2012). One cool thing about a being a Microsoft MVP was access to the internal developer teams. Everyone could file Microsoft Connect items reporting bugs and making suggestions for product improvements. Many MVPs did so only to have their bug reports marked as “works as designed” or “won’t fix” and suggestions responded to with something similar. It was discouraging. There are reasons many Connect items were addressed in this way. I am happy to report the root cause (Performance-Based Management or PBM) has been abandoned and the Microsoft Developer Teams are really and truly listening and responding to requests from the field.
Why I Built DILM Suite, by Andy Leonard
That doesn’t mean every suggestion is acted upon (I promise this is not a complaint). It turns out that Microsoft is a software development enterprise. As big as Microsoft is, they cannot possibly respond to every request. When I realized this, I began thinking about how I might address gaps I perceived. I’d co-founded a consulting company and we (collectively) weren’t interested in becoming a software product company. But I was very interested in developing products to address gaps in Data Integration Lifecycle Management (DILM).
In 2015 I left the consulting company I co-founded and immediately began developing the software I’d dreamed of building. In my opinion, the most fair answers to the question, “Why?” are:
- I came to believe the Microsoft SSIS Developer Team would never address the things I perceived as “gaps” in the product story; and
- I came to believe the consulting company I co-founded and I held irreconcilable visions of how to address DILM issues.
Looking back with two years of perspective, I believe focusing on DILM was the best long-term move for me. I started another consulting company, Enterprise Data & Analytics (entdna.com), mostly to fund my coding habit.
Surfacing the SSIS Catalog
Let’s examine the SSIS Catalog surface in the SSMS Object Explorer’s Integration Services Catalogs node as shown in Figure 6-1:
Beneath the Integration Services Catalogs node we find the SSIS Catalog named SSISDB. Two Catalog Folders are displayed, Framework and Test. The Test Folder contains Projects and Environment virtual folders. The Projects virtual folder contains our SSIS Catalog Project named DILMSample, which in turn contains our SSIS Package named SimplePackage.dtsx. The Environments virtual folder contains our Catalog Environment named envConnection1.
We know – because we’ve done the work – that there’s more there than meets the eye.
6.1 SSIS Catalog Environment Configuration
If we double-click envConnection1 we can see details of our Catalog Environment Variable on the Variables page as shown in Figure 6-2:
The Variables page contains details about SSIS Catalog Environment Variables including name, data type, description, value, and whether the variable is sensitive.
6.2 SSIS Catalog Project Configuration
The Parameters tab on the Parameters page of the SSIS Catalog Project Configuration dialog lists SSIS Project and Package parameters, their container name, and value by default as shown in Figure 6-3:
The Connection Managers tab of the Parameters page contains a list of SSIS Project and Package connection managers and their properties as shown in Figure 6-4:
The References page of the SSIS Catalog Project Configure dialog contains a list of SSIS Catalog Environments the SSIS Catalog Project may reference at runtime as shown in Figure 6-5:
That’s a lot of right- and double-clicking just to see what’s configured in an SSIS Catalog Project.
6.3 Catalog Browser
The SSIS Catalog is filled with really cool and useful configuration information, but one has to know where to look and – in some cases – where to look isn’t so obvious.
Enter Catalog Browser, a free utility that is part of the DILM Suite and available at dilmsuite.com/catalog-browser. Catalog Browser was built to surface the contents of the SSIS Catalog in a single view – a tree that exposes all relevant SSIS Catalog artifacts, properties, and configurations.
As shown in Figure 6-6, Catalog Browser surfaces the same metadata as the SSMS Object Explorer Integration Services Catalogs node:
Looking at Figure 6-6, though, you probably already see some differences between Catalog Browser and the SSMS Object Explorer Integration Services Catalogs node. Please note the Project Parameters and Project References virtual folders present beneath the SSIS Catalog Project, in addition to the Packages virtual folder.
Expanding these virtual folders reveals the SSIS Catalog Project Parameters and Reference as shown in Figure 6-7:
Please remember in Figure 6-3 the SSMS Object Explorer Integration Services Catalogs node surfaced all parameters – SSIS Catalog Project Parameters and SSIS Package Parameters. Where are the Package Parameters? They’re here in Catalog Browser. To view Package Parameters, expand the SimplePackage.dtsx SSIS Package node as shown in Figure 6-8:
Please recall Connection Manager Properties are treated as Parameters in the SSIS Catalog. They are prefixed with “CM.”. We see the SSIS Package Connection Manager vmDemo\Demo.TestDB1 Connection String property is mapped to an SSIS Catalog Environment Variable named ConnectionString.
To surface the Reference used for the Reference Mapping, expand the Package References virtual folder as shown in Figure 6-9:
Expanding the Package Reference virtual folder surfaces the Test/envConection1 Catalog Environment. Expanding the Test/envConection1 Catalog Environment reveals the Catalog Environment Variable named ConnectionString is mapped to the vmDemo\Demo.TestDB1 Connection String property.
But what’s the value of the ConnectionString Catalog Environment Variable? Expand the envConnection1 Catalog Environment in the Environments virtual Folder to view the collection of Catalog Environment Variables, their data types, and their values as shown in Figure 6-10:
SSIS Package Properties includes a Package Version property constructed from the Version Major, Version Minor, and Version Build properties of the SSIS package. Every time a developer saves an SSIS package, the Version Build property increments. It’s possible to revise an SSIS package and “trick” the Version Build property by manually setting it. I have not yet found a valid use case for doing so to SSIS Catalog-deployed SSIS packages.
The Package Version property can be used to detect different versions of SSIS packages deployed to an SSIS Catalog. Because SSIS developers can manually set the Version Build property, Package Version is not a reliable indication.
The Package Properties virtual folder surfaces SSIS Package metadata as shown in Figure 6-11:
Catalog Properties are handy for detecting differences in patch levels (via the Schema Build property). Catalog Version is a property exposed by Catalog Base – the custom Catalog object that lies beneath Catalog Browser.
Catalog Base works with SSIS 2012, 2014, and 2016 Catalogs.
An update that also works with SSIS 2017 is under development.
The Catalog Properties virtual folder surfaces SSIS Catalog metadata as shown in Figure 6-12:
Catalog Browser surfaces SSIS Catalog artifacts, configurations metadata, and artifact properties in a single view.