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:

1. I came to believe the Microsoft SSIS Developer Team would never address the things I perceived as “gaps” in the product story; and
2. 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:

Figure 6-1. The SSIS Catalog as Shown in the SSMS Object Explorer Integration Services Catalogs Node

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:

Figure 6-2. Viewing the Variables Page of an SSIS Catalog Environment

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:

Figure 6-3. Viewing Project Parameters and Values for an SSIS Catalog Project

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:

Figure 6-4. Viewing Connection Manager Parameters and Values for an SSIS Catalog Project

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:

Figure 6-5. Viewing Project References for an SSIS Catalog Project

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:

Figure 6-6. Catalog Browser Surfacing Part of the SSIS Project and Configurations Metadata

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:

Figure 6-7. SSIS Catalog Project Parameters and References

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:

Figure 6-8. Viewing SSIS Package Parameters

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:

Figure 6-9. Viewing the Package Reference

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:

Figure 6-10. Catalog Environment Variables, Data Types, and Values

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:

Figure 6-11. SSIS Package Properties

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:

Figure 6-12. SSIS Catalog Properties

Conclusion

Catalog Browser surfaces SSIS Catalog artifacts, configurations metadata, and artifact properties in a single view.