THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is

Announcing SSIS Catalog Compare and CatCompare Version 2

CatCompareI am pleased to announce the release of SSIS Catalog Compare and CatCompare version 2. You can purchase a bundle that includes both products (and saves you some money) or can purchase each product separately: SSIS Catalog Compare and CatCompare.

If you’re interested in learning more about the functionality provided by the products you can download (or view) the documentation for free.

A Couple CatCompare Examples

Two of my favorite chunks of functionality are the CatCompare commands: Deploy Folder Differences and Deploy Catalog Differences.

Deploy Folder Differences

Deploy Folder Differences can be executed after you load a couple Catalogs into the CatalogBase (an object I built to model the SSIS Catalog) server objects. They must be compared to detect the differences, and then the differences in one Catalog Folder in a Catalog instance can be overwritten by the version in the other Catalog’s Folder. The following command line accomplishes that, redirects the output to a text file, and then exits:

"C:\Program Files\DILMSuite\SSISCatalogCompare\CatCompare.exe" "-server0;vmSql16\Dev" "-server1;vmSql16\Test" "-comp" "-deployfolderdiffs;\vmSql16_Dev\IS\SSISDB\Folders\Demo\|0|\vmSql16_Test\IS\SSISDB\Folders\Demo>E:\Test\CatCompare_DeployFolderDiffs_Results.txt" "-exit"

The command line above loads the Catalogs hosted on the vmSql16\Dev and vmSql16\Test SQL Server instances. The catalogs are compared, and then the differences identified by the compare operation between the Demo folder in each Catalog are deployed from the vmSql16\Dev Demo folder to the vmSql16\Test Demo folder.

Why do I like this command? I can run it every night to collect the changes a data integration developer deployed to her Dev Catalog, deploying the updates (only) to an integration SSIS Catalog instance. You know, just like the C# developers when they practice DevOps.

Deploy Catalog Differences

The Deploy Catalog Differences command will help keep two Catalogs in sync:

"C:\Program Files\DILMSuite\SSISCatalogCompare\CatCompare.exe" "-server0;vmSql16\Dev" "-server1;vmSql16\Test" "-comp" "-deploycatalogdiffs;0>E:\Test\CatCompare_DeployCatalogDiffs_Results.txt" "-exit"

As before, two Catalogs are loaded and compared. Things that are different in server0 (vmSql16\Dev) are deployed to server1 (vmSql16\Test). The results are redirected to a file and the utility exits.

Why do I like this command? I can use it to keep a “warm copy” of the Test SSIS Catalog somewhere else in the enterprise. This is handy if two (or more) data integration teams are developing updates to coupled projects.


My long-term goal with DILM Suite products is to facilitate DevOps and Data Integration Lifecycle Management with SSIS.

Kent Bradshaw and I will be demonstrating SSIS Catalog Compare, CatCompare, and more DILM Suite utilities in the upcoming (free!) webinars: SSIS Catalog Management (10 Jan) and Advanced SSIS Execution (24 Jan).


Learn More:
SSIS Catalog Management – 10 Jan 2017
Advanced SSIS Execution – 24 Jan 2017

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

Published Friday, January 6, 2017 3:37 PM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


This Blog



My Latest Book:

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement