SSISDB Catalog Database
In earlier versions of SQL Server Integration Services (SSIS), we used to keep SSIS packages either on a file share or in the MSDB database and configuration parameters such as connection strings and sensitive data either in an XML file or in a SQL Server table. The downside to this was that this was unprotected.
Starting with SQL Server 2012, these kind of concerns have been addressed by Microsoft with the help of the SSISDB Catalog. With a new installation of SQL Server 2012 or 2014, the SSIS catalog is not installed by default.
As per MSDN “The SSISDB catalog is the central point for working with Integration Services (SSIS) projects that you’ve deployed to the Integration Services server. For example, you set project and package parameters, configure environments to specify runtime values for packages, execute and troubleshoot packages, and manage Integration Services server operations. The objects that are stored in the SSISDB catalog include projects, packages, parameters, environments, and operational history.”
The SSISDB catalog stores the packages that you’ve deployed to the Integration Services server, but before you can deploy the projects to the Integration Services server, the server must contain the SSISDB catalog. From a security stand point, the SSISDB catalog is secure to store all your sensitive information as well. When you deploy an Integration Services project to the Integration Services server after creating the SSISDB catalog, the catalog automatically encrypts the package data and sensitive values. The catalog also automatically decrypts the data when you retrieve it. A database master key is used for the encryption and the key is created when you create the catalog.
Creating the SSIS Catalog SSISDB
You will see a new folder named “Integration Services Catalogs” in SQL Server Management Studio (SSMS) if you connect to a SQL Server 2012 or later instance. As I said in the problem section, by default the SSISDB catalog will not be installed so before deploying SSIS packages to this catalog on any instance, we would have to create the SSISDB catalog. Here I will show you how to create SSISDB catalog on a SQL Server instance. Before moving forward, make sure you have installed SQL Server Integration Services and the shared components during the SQL Server installation.
Step 1: Launch SQL Server Management Studio and connect to the SQL Server instance where you want to create the SSIS catalog. You will be allowed to create it on SQL Server 2012 or later instances.
Step 2: You will see a new node named “Integration Services Catalogs” in SQL Server Management Studio in the left side pane of SSMS. Right click on “Integration Services Catalogs” and choose “Create Catalog…” as shown in the below screenshot.
Step 3: A window named “Create Catalog” will appear for further processing and to complete the required parameters.
Step 4: Click on “Enable CLR Integration” because the catalog uses CLR stored procedures. Once you will click on this option, another checkbox will be enabled named “Enable automatic execution of Integration Services stored procedure at SQL Server startup”. Click on this check box to enable the catalog startup stored procedure to run each time the SSIS server instance is restarted. Enter the password to protect the database master key that is used for encrypting the catalog data. This password is very important, so make sure to remember this password or save it in a secure location. The password is required, because the catalog stores sensitive information (such as database name and passwords) in the SSIS catalog. Now click on the “OK” button.
Once you click on the “OK” button, the SSISDB catalog will be created under the Integration Services Catalogs node and a new user database named “SSISDB” will be created under the Databases node. The SSIS catalog will now use this database to store SSIS related information unlike previous versions where such information was stored in MSDB. See the below screenshot, both the SSISDB catalog under Integration Services Catalogs node as well as the user database SSISDB under the Databases node is now showing. You are now done with the SSISDB catalog creation.
Comments