Problem
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. This tip will demonstrate you how to easily and quickly set up the SSIS catalog on a new installation of SQL Server.
Solution
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.
Possible Issues when Creating the SSIS Catalog SSISDB
Sometimes you will see the below issue when creating the SSISDB catalog. One possible reason might be because of an improper installation of SQL Server Integration Services or the absence of SQL Server Integration Services so make sure SQL Server Integration Services was properly installed.
The catalog backup file ''E:\MSSQL\120\DTS\Binn\SSISDBBackup.bak' could not be accessed. Make sure the database file exists, and the SQL Server service account is able to access it. (Microsoft.SqlServer.IntegrationServices.Common.ObjectModel)
The reason behind the above error is the absence of Integration Services because it was not installed. Once Integration Services is installed the SSISDBBackup file and other binaries will be in the Binn folder. You can also fix this issue by copying the SSISDBBackup.bak file from any server which is running SQL Server 2012 or later to your target Binn folder.
Renaming the SSIS Catalog SSISDB
The SSISDB database should not be renamed. You can rename it, but the SSISDB catalog under Integration Services Catalog node will not be accessible anymore and it will disappear from the node. If you try to access the SSISDB catalog under the Integration Services Catalog node after renaming SSISDB, it will throw an error as shown below:
I renamed the SSISDB user database to SSISDB_rename in the above screenshot and then I tried to access the SSISDB catalog under the Integration Services Catalog node. If you refresh the Integration Services Catalog node after renaming the SSISDB database, the SSISDB catalog will disappear, but it will reappear and be accessible when you rename it back SSISDB. The below screenshot is the before and after renaming as a comparison.
Next Steps
- Once the SSISDB catalog has been created, go ahead and start deploying your SQL Server Integration Services projects using the SSISDB catalog.
- Read more SQL Server Integration Services Tips
