Viewing SSIS Configurations Metadata in SSIS Catalog Browser

SSIS Catalog Browser is a pretty neat product. “How neat is it, Andy?” I’m glad you asked.

It’s free. That makes it difficult to beat the cost.

SSIS Catalog Browser is designed to surface all SSIS Catalog artifacts and properties in a single view. “What exactly does that mean, Andy?” You’re sharp. Let’s talk about why the surface-single-view is important.

Before I go on, you may read what I’m about to write here and in the companion post and think, “Andy doesn’t like the Integration Services Catalogs node in SSMS.” That is not accurate. I do like the Integration Services Catalogs node in SSMS. It surfaces enough information for the primary target user of SSMS – the Database Administrator – to see what they need to see to do their job, without “cluttering up” their interface with stuff that they rarely need to see and even more-rarely change.

In the companion post I shared this image of the windows (and pages) you need to open in SSMS to view the configured execution-time value of a parameter that is mapped via reference:

(click to enlarge)

That’s a lot of open windows.

So how does one view the same configuration metadata in SSIS Catalog Browser?

Under the Project node (LiftAndShift), we find a virtual folder that holds Project Parameters.

In Project parameters, we find a reference mapping – indicated by underlined text decoration and describing the reference mapping as between the parameter (ProjectParameter) and the SSIS Catalog Environment Variable (StringParameter).

Expanding the reference mapping node surfaces References. There are two references named env1 and env2. Since references can reference SSIS Catalog Environments in other Catalog folders, the fully-qualified path to each SSIS Catalog environment is shown in folder/environment format.

Expanding each reference node surfaces the value of the SSIS Catalog Environment Variable in each SSIS Catalog Environment.

I call this feature Values Everywhere, and I like it. A lot.

Values Everywhere From the Project Reference Perspective

Values Everywhere is perspective-aware. Whereas from the perspective of an SSIS Project Parameter, Values Everywhere surfaces the reference mapping in the format parameter–>environment variable, in the Project References virtual folder, Values Everywhere surfaces the same relationship as environment variable–>parameter:

Values Everywhere perspectives follow intuition when surfacing reference mapping relationships. (Did I mention I like this feature? A lot?)

Conclusion

SSIS Catalog Browser provides a clean interface for enterprise Release Management and Configuration teams. And it’s free.

I can hear you thinking, “Why is Catalog Browser free, Andy?” I give away Catalog Browser to demonstrate the surfacing capabilities of SSIS Catalog Compare.

SSIS Catalog Compare

SSIS Catalog Compare not only surfaces two SSIS Catalogs side by side, you can compare the contents of the Catalogs:

You can also script an entire SSIS Catalog which produces T-SQL script and ISPAC files for every artifact in the SSIS Catalog (organized by instance and folder):

You can also deploy all artifacts contained in an SSIS Catalog Folder from one SSIS Catalog to another:

This functionality is an efficient method for Data Integration Lifecycle Management – or DevOps – with SSIS.

SSIS Catalog Compare even works with Azure Data Factory SSIS Integration Runtime, so you can use SSIS Catalog Compare to lift and shift SSIS from on-premises Catalogs to the cloud.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.