Help for DBAs Who Support SSIS…

Andy Leonard, 2017-06-19

…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.





Related content

Reproduced with kind permission from the blog of Ashvini Sharma (MSFT)

InfoPath forms can be saved to XML, these XML Files can later be used in SSIS XMLSource adapter to pull out the data in tables and columns. However, there are some common problems you may meet in these scenarios. This article describes how to work around these potential problems. The issues mentioned in this article is not only specific to InfoPath files, it can also be referenced in other similar situations as well.

Additional Articles


1,328 reads

Easy Package Configuration

One of the age old problems in DTS is moving packages between your development, test and production environments. Typically a series of manual edits needs to be done to all the packages to make sure that all the connection objects are pointing to the correct physical servers. This is time consuming and gives rise to the possibility of human error, particularly if the solution incorporates many DTS packages. Many companies have provided their own custom solutions for managing this problem but these are still workarounds for a problem that is inherently DTS's.

Additional Articles


1,761 reads