Understanding the SQL Server Integration Services Catalog and creating the SSISDB Catalog

By:   |   Updated: 2015-11-24   |   Comments (11)   |   Related: > Integration Services Configuration Options


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.

Right Click on Integration Services Catalogs and choose create catalog

Step 3: A window named "Create Catalog" will appear for further processing and to complete the required parameters.

Create Catalog Window

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.

Create Catalog Window with details

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.

SSISDB database and catalog in SSMS

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.

error_during_Create_Catalog

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:

Rename SSISDB Database

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.

Rename SSISDB Database before and after rename
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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2015-11-24

Comments For This Article




Monday, September 30, 2024 - 7:51:25 PM - Richard Back To Top (92539)
My error:
When I try to create SSISDB, I get this error:
"Cannot create the Catalog SSISDB because it errors out with "cleanup_server_retention_window' - cannot find or no permission. How to solve?

Tuesday, May 15, 2018 - 10:23:25 AM - Ghassen Back To Top (75947)

Hello,

Is it possible to have Integration Services Catalogs folder in the SQL Server instance even if SQL Server Integration Services is not installed on the server ?

Thanks in advance.

Regards,

Ghassen

 

Wednesday, January 24, 2018 - 7:01:22 AM - Hany Back To Top (75030)

 

Thanks a million, you saved my day!!

 

Friday, December 15, 2017 - 6:20:26 AM - Soumik Back To Top (74040)

 Hi Manvendra,

My question is, after the catalog creation and ssis project deployment, can we turn off the CLR integration ? and if we do so, will the jobs executing packages fom the catalog affected in any way ?

Looking forward to your response.

Thanks!

-Soumik

 

Monday, December 7, 2015 - 12:13:06 AM - Deepak Back To Top (40203)

Thanks Manvendra, The is is so simple and valuable as well. Request you to keep posting such tips.

 

 

Tuesday, December 1, 2015 - 2:50:23 AM - Manvendra Back To Top (40165)

Thank you everyone for your valuable feedbacks

Tuesday, December 1, 2015 - 2:49:25 AM - Manvendra Back To Top (40164)

Thank you for your feedback Grumpy.

 

It depends on the protection level of the package ..if its set to not encrypt the data..or package is saved in  file system......All data would be unprotected..

Wednesday, November 25, 2015 - 11:15:53 AM - Grumpy Old DBA Back To Top (39141)

Nice article. The comments below are meant to be constructive. (I don't care if you don't publish this but just update your article.)

Your first paragraph ends with "The downside to this was that this was unprotected."

This is not true. See https://msdn.microsoft.com/en-us/library/ms141747%28v=sql.105%29.aspx.

Dealing with package passwords can be a real pain. I'm glad to see things being simplified.

Also, many DBAs have many instances to manage and using a GUI is not practical. So can this be scripted?

I found this: https://msdn.microsoft.com/en-us/library/gg471509.aspx.

I have not verified it.

 

 

Wednesday, November 25, 2015 - 2:43:15 AM - anoop Back To Top (39137)

Thanks for tip. It explains the concept very clearly.

 

 

Tuesday, November 24, 2015 - 3:14:59 PM - Golam Kabir Back To Top (39133)

Nice and detailed - thanks. Pls keep posting.

Tuesday, November 24, 2015 - 12:52:22 PM - Garland MacNeill Back To Top (39131)

Great post. A good follow up would be an explaination of persmissions and how to set them up for the SSISDB catalog.














get free sql tips
agree to terms