In my SQL Server Central article, Help for DBAs Who Support SSIS, I walk through several DILM (Data Integration Lifecycle Management) use cases including:
- Surfacing SSIS Catalog metadata
- Comparing SSIS Project configurations in two SSIS Catalogs
- Scripting SSIS Project ISPAC files
- Deploying an SSIS Project from one SSIS Catalog directly to another SSIS Catalog
- Generating scripts for an SSIS Catalog Folder and all Folder contents, including:
- SSIS Projects
- SSIS Catalog Environments
- SSIS Catalog Environment Variables
- References
- Reference Mappings
- Literal Overrides
One feature I did not cover in the article is one of my favorite features: Generate All Catalog Scripts. This feature is especially useful if your enterprise is practicing (or desires to practice) DevOps with SSIS because it facilitates standing up a Test or QA SSIS Catalog quickly.
After connecting to an instance of an SSIS Catalog, simply right-click the SSISDB node and click Generate All Catalog Scripts:
You will be prompted to select (or create) a folder for the scripts:
When you click the OK button, scripts for the contents of the SSIS Catalog are created! The directory structure inside the folder you selected matches the SSIS Catalog folders, beginning with a directory named after the SQL Server instance that hosts the SSIS Catalog you scripted. Inside the instance directory you find a directory for each SSIS Catalog folder – each containing scripts for the objects contained in that folder plus their dependencies (click to enlarge):
To learn more and see the helpful short video demonstrations of SSIS Catalog Compare, please visit the SSIS Catalog Compare page at DILM Suite. Don’t take my word for it, please read the testimonials at the site! One of my favorites is:
“I tried to reconcile two SSIS Catalogs for a week. I used SSIS Catalog Compare and was finished in less than one hour.”
– an Enterprise Architect
Comments