The SSIS Catalog is a pretty cool SSIS framework, whether installed on-premises or in the cloud (Azure-SSIS).
Practicing lifecycle management with the SSIS Catalog can be… challenging. Try scripting an existing SSIS Catalog Environment after creation, for example:
The result?
SCCPAC Files to the Rescue!
SSIS Catalog Compare Enterprise Edition includes SCCPAC file generation. SCCPAC files simplify SSIS Catalog management. SCCPAC files may be deployed using the SCCPAC Deployment Wizard.
Anatomy of an SCCPAC File
I can hear you thinking, “So what’s in an SCCPAC file, Andy?” That’s an excellent question. I am glad you asked!
Let’s start with a deployment of a very simple SSIS project to an SSIS Catalog. In Visual Studio, our Solution Explorer appears as shown:
The SSIS package – named “SSIS Package.dtsx” – is empty:
Once deployed to an SSIS Catalog in an SSIS Catalog Folder named “SSIS Catalog Folder,” the SSIS project appears in the SSMS Object Explorer’s Integration Services Catalogs node as shown here:
By the way, the same SSIS project is displayed in SSIS Catalog Browser – a free utility from DILM Suite – as shown:
What’s Inside an SCCPAC File?
An SCCPAC file is a zip file that contains T-SQL scripts and SSIS project ISPAC files named with a numbered prefix to indicate dependencies. You may open an SCCPAC file using any zip-compatible file compression utility, such as 7-zip:
The SCCPAC contents displayed above represent our simple project named “SSIS Project” deployed to an SSIS Catalog Folder named “SSIS Catalog Folder”.
When opened in the SCCPAC Deployment Wizard, the SCCPAC file displays T-SQL and ISPAC files – in dependency order of deployment:
Connect to a Target Server – either on-premises or in Azure-SSIS – and click the Execute button to deploy the contents of the SCCPAC file to the SSIS Catalog hosted on the target SQL Server instance:
That’s it.
Generating SCCPAC Files
I can hear you thinking, “Great, Andy. How do I generate SCCPAC files?” Another great question – you are on a roll today!
The “SCC” in SCCPAC stands for SSIS Catalog Compare, and SSIS Catalog Compare Enterprise Edition generates SCCPAC files.
SCCPAC files are generated when one executes one of two commands in SSIS Catalog Compare; “Generate Scripts for Folder and Contents” or “Generate All Catalog Scripts” as shown below:
After SCCPAC files are generated, double-click (or right-click and then click “Open”) the SCCPAC file to open the file in the SCCPAC Deployment Wizard, as demonstrated in this more complex example:
This larger SCCPAC file opens the SCCPAC Deployment Wizard and displays the constituent files shown below:
Conclusion
SCCPAC files are designed to support DevOps and Data Integration Lifecycle Management in modern enterprise data engineering.
Need Help?
Enterprise Data & Analytics maintains DILM Suite, a site filled with mostly-free (and some open-source) helpful SSIS lifecycle management utilities, and offers SSIS consulting and training. How may we serve you today?
One thought on “The SSIS Catalog and SCCPAC Files”