…and use the SSIS Catalog, SSIS Catalog Compare is for you. SSIS Catalog Compare can help you do your job by:
- Surfacing SSIS Catalog metadata
- Comparing SSIS projects
- 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.
Conclusion
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.
