SSIS Catalog Browser has been updated! Catalog Browser is part of the DILM Suite of utilities designed to assist enterprises with Data Integration Lifecycle Management (DILM).
And it’s free.
Catalog Browser provides a rich view of SSIS Catalog contents, configuration, and metadata. The utility surfaces properties, environment variables, environment variable values, references, reference mappings, and literal overrides without the need to open additional dialogs.
New Features
Catalog Properties
Catalog Properties now display catalog metadata including:
-
Maximum Project Versions
-
Operation and Version Cleanup Enabled
-
Retention Window
-
Default Server Logging Level
-
SSIS Catalog Schema Build
-
Catalog Version
-
And more!
Catalog Version is a Catalog Base property added to the standard collection of SSIS Catalog properties. Catalog Base is a custom .Net library we built to represent the SSIS Catalog.
Virtual Folders Sort Order
The order of the Projects and Environments virtual folders now match that of the Integration Services Catalogs node in SQL Server Management Studio’s (SSMS’s) Object Explorer. Why is this important? Visual cues and defaults count. We don’t want to confuse anyone by doing things differently than they have come to expect.
Package Properties
Package Properties are an important addition to Catalog Browser (and SSIS Catalog Compare). Perhaps the most important property is Package Version which is displayed in the format <Major Version>.<Minor Version>.<Version Build>. I can hear you thinking, “Why is the Package Version property so important, Andy?” I’m glad you asked! Although all three values may be edited from the SSIS Package properties window, Version Build auto-increments each time an SSIS Package is saved in SQL Server Data Tools (SSDT). Therefore, it can be used as a (potential) flag to indicate differences in SSIS Package deployments.
It’s possible to manually update the value of an SSIS Package’s Version Build property from SSDT, so comparing the Package Version property is not a completely reliable mechanism for determining if two different SSIS Package deployments are identical. Because editor metadata is also stored in SSIS Package XML, two deployments can be functionally identical and yet have different versions.
TL;DR Regarding SSIS Package Version Build Property Values
Why are we displaying Package Version in SSIS Catalog Browser (and comparing Package Version in SSIS Catalog Compare)? Because even though Version Build can be manually manipulated, we believe the Package Version property provides some indication that the packages match. We cannot think of a use case for changing an SSIS Package deployed to the SSIS Catalog and then manually manipulating the Version Build property so that it matches the Package Version of the old SSIS Package. But we could be wrong. Please let us know if you have such a use case.
When executing SSIS Packages stored outside the SSIS Catalog using dtexec, dtexecui, or SQL Agent, one verification option is Verify Package Build (/VerifyBuild):
It’s possible this option is set for SSIS package execution and that your enterprise chooses to maintain a consistent Version Build property value so that this verification succeeds and does not need to be updated.
In my opinion, the Verify Package Build option should not be managed by maintaining the value of the SSIS Package’s Version Build property. Rather, the value stored in the execution engine should be updated to reflect the latest SSIS Package Version Build property value.
I am not aware of a Version Build verification for SSIS Packages stored in the SSIS Catalog. SSIS Catalog Compare will compare the Package Version values between two SSIS Packages stored in different SSIS Catalogs. SSIS Catalog Compare does not detect a mismatch between two packages in the same Catalog Folder and Catalog Project with the same Package Version metadata. We (and others) have looked into comparing SSIS Package versions to detect the differences (or lack of differences). Because DTSX files are XML and because the XML in SSIS Packages can (and does) “move around physically” in the file without changing logical functionality, this would be a semantic comparison. That’s one reason (not the only reason) why reliably comparing SSIS Packages is hard.
Enjoy the new version of SSIS Catalog Browser!
:{>
Learn More:
SSIS Academy: Using the SSIS Catalog – Three free webinars 27-29 Dec
Data Integration Lifecycle Management (DILM) Suite – free (mostly) and not-free software to help you manage SSIS in the enterprise.
Deploying SSIS Projects to a Restored SSIS Catalog (SSISDB)
Coming Soon: A Command-Line Interface for Managing SSIS Catalogs
Stairway to Integration Services
Related Training:
SSIS Lifecycle Management (free recording, registration required)
IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago
IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago
Need help implementing an SSIS solution?
Contact Enterprise Data & Analytics today!
Comments