SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Help for DBAs Who Support SSIS…

By Andy Leonard,

…and use the SSIS Catalog, SSIS Catalog Compare is for you. SSIS Catalog Compare can help you do your job by:

  1. Surfacing SSIS Catalog metadata
  2. Comparing SSIS projects
  3. Scripting SSIS Catalog configurations

SSIS Catalog Compare Surfaces SSIS Catalog Metadata

SSIS Catalog Compare surfaces the entire SSIS Catalog in a single view – no need to open additional dialogs to find configurations metadata, as shown in Figure 1:

Figure 1

In Figure 1, we see the String Project Parameter named MedicalDatabaseConnectionString is mapped via Catalog Reference to an Environment Variable (also) named MedicalDatabaseConnectionString. The Project Reference linking the MedicalDataDemo SSIS project to the SSIS Catalog Environment named envMedicalData (stored in the Demo folder) is shown in the Project References virtual folder. Finally, the value of the MedicalDatabaseConnectionString Environment Variable configured in the envMedicalData Catalog Environment is surfaced in the Environments virtual folder.

SSIS Catalog Compare Compares SSIS Projects and Configurations Metadata

SSIS Catalog Compare compares SSIS projects and configurations in different SSIS Catalogs. Have you ever experienced a failure due to differences between a Production environment and Development, Test, and/or QA environments? I have and it can be a messy affair.

Differences are indicated by tree node shading. Differences in child nodes are indicated by italics node text for all parent nodes, as shown in Figure 2:

Figure 2

SSIS Catalog Compare compares SSIS Catalog Folders, Projects (shown in Figure 3 below), Package version metadata (shown in Figure 2), Package parameter values (shown in Figure 2), Environments, Environment Variable Values (shown in Figure 3), References, and Reference Mappings:

Figure 3

You can use SSIS Catalog Compare to quickly identify differences in SSIS Catalog instances. After identifying the differences, you can generate scripts to “sync up” SSIS projects.

SSIS Catalog Compare Scripts SSIS Catalog Configurations

Assume our enterprise is testing a new version of a data warehouse ETL project. We’re ready to promote the code to the QA environment and allow power users to put the process through its paces. We desire to start our QA process with QA matching Prod. We used SSIS Catalog Compare to and learned the version of the MedicalDataDemo project currently deployed to QA is different from the version currently deployed to Prod (see Figure 2). How can we remedy this version mismatch?

We have several options for synchronizing QA with Prod. Option 1 is to deploy objects from Prod by right-clicking them in the SSIS Catalog Browser treeview and clicking Deploy ___. In Figure 4 we are deploying the SSIS Project from the Prod SSIS Catalog to the QA SSIS Catalog:

Figure 4

We can export the ISPAC file from the Prod Catalog instance as shown in Figure 5:

Figure 5

After the export completes and the ISPAC file is stored in the file system, we can then execute the ISPAC file to deploy the SSIS Project using the Integration Services Deployment Wizard.

Figure 6 shows another way to synchronize differing SSIS Catalog Folder contents, we can generate scripts for the entire SSIS Catalog Folder and its contents:

Figure 6

Once a file system location is selected, SSIS Catalog Compare creates a folder structure that includes the SQL Server instance friendly name, the SSIS Catalog Folder name, and then scripts and ISPAC files for each SSIS Project and Environment stored within the folder (and their dependencies) as shown in Figure 7:

Figure 7

The T-SQL scripts and ISPAC file are numbered to reflect dependencies.


SSIS Catalog Compare is part of the DILM (Data Integration Lifecycle Management) Suite. The DILM Suite supports enterprise data integration by providing several utilities, many of them are free of charge at the time of this writing. For more information, contact Andy Leonard. To keep abreast of offers, updates, and free webinars, please join the DILM Mailing List.

Total article views: 738 | Views in the last 30 days: 40
Related Articles

Creating a Robust SSIS Development Environment using the SSIS Catalog

Discover how to integrate features of Visual Studio and the SSIS Catalog for an efficient developmen...


SSIS Catalog Environments– Step 20 of the Stairway to Integration Services

In this next level of the Stairway to Integration Services, we look at the SSIS catalog environments...


SSIS Catalog Project Versioning

If you’ve spent much time working with the SSIS catalog, you likely already know that the catalog is...


How to run SSIS Project as a SQL Job

In this piece by Pilip Horan, learn how to create an SSIS catalog and run a project as a SQL Agent J...


Scripting Environments in SSIS

Demo and script on how to script out SSIS Environments and their associated variables