From SQL Server 2012 onwards, Microsoft introduced a new deployment model in SSIS called the project deployment model. This new model has many benefits in managing SSIS project deployments, executions, and configurations. These are stored in a standard SQL Server database called SSISDB. Every new version of SQL Server also upgrades the SSIS Catalog with new features. In this article, I will describe how to create SSIS Catalog in SQL Server to deploy SSIS projects and their configurations.
The SSIS Integration Services Catalog consists of the following artifacts:
- SSIS Builds (.ispac files) – Contains several SSIS packages with project and package parameters.
- Environments – Stores configurations of SSIS projects. These variables are used to configure the SSIS project and package parameters of SSIS projects.
Pre-requisites:
1. User should have sufficient rights to create SSISDB Catalog under Integration Services Catalog in SQL Server.
Steps to create SSIS Catalog in SQL Server.
1. Connect to SQL Server using SQL Server Management Studio (SSMS). Right-click on Integration Services Catalog and select Create Catalog option.
2. On Catalog Creation Wizard, Enable CLR Integration. Provide a password for data encryption. Store this password for the future. And click OK.
This should create SSISDB catalog under Integration Services Catalog. You can now start deploying projects and executing SSIS packages.
Pro tips:
1. You can only create one SSISDB per SQL Server.
2. Steps to create SSISDB in Azure SSIS runtime are different; refer to this post to achieve this.
3. If you want to migrate an existing SSISDB from one SQL Server to another, you can use SSIS Catalog Migration Wizard for seamless migration in a few minutes.