If you are building SQL Server Integration Services (SSIS) packages, using the SSIS catalog as a deployment target is usually the easiest and most efficient solution. However, the SSIS catalog is not created by default, even when you select the SSIS components during the SQL Server installation process. The good news is that creating the SSIS catalog is a quick and simple process.
In this brief post, I’ll demonstrate how to create the SSIS catalog.
Creating the SSIS Catalog
When installing a new instance of SQL Server, you’ll have the option to install SQL Server Integration Services, and this option should be selected for any instance that will store and run SSIS packages. However, doing so will not create the SSIS catalog. This must be done manually in SQL Server Management Studio as shown below.
In SSMS, you’ll see an Object Explorer folder named Integration Services Catalogs when you connect to any instance running SQL Server 2012 or newer.
In this example, that node is empty, which means that there is no SSIS catalog on this instance of SQL Server.
To create the SSIS catalog, simply right-click on the Integration Services Catalogs folder in Object Explorer, and choose Create Catalog. In doing so, the Create Catalog dialog box will appear as shown below.
Although there are very few configuration options in the initial configuration, here are a few options that you can set:
- Enable CLR integration. This is a required setting, as SQL CLR must be enabled on this instance to create the SSIS catalog. This box must be checked to continue.
- Enable automatic execution of Integration Services stored procedure at SQL Server startup. This optional setting allows the execution of startup procedures for background SSIS processes, such as cleaning up from a failed execution. I haven’t found any reason not to enable this, so I always turn it on.
- Name of the catalog database. Technically, you don’t have an option here – the catalog will always be named SSISDB.
- Password. This is the password used to encrypt sensitive values such as stored passwords. Although you won’t need to use this password in day-to-day interaction with the SSIS catalog, you’ll need it in cases of disaster recovery, or if the SSISDB database needs to be moved. Use a secure password, and make sure you store it in your password management tool.
Once these items are configured, you’re ready to create the catalog. When you click OK, the catalog will be created, and you’ll see the new catalog (SSISDB) in the Integration Services Catalogs folder. You’ll also find a new database with the same name in the list of user databases.
Next Steps
Once the SSIS catalog is created, you’re ready to start deploying SSIS projects! Keep in mind that the SSISDB database is just a regular user database, so be sure to add it to your scheduled database backup job to avoid data loss.
Conclusion
In this post, we reviewed the steps required to instantiate the SSIS catalog. Creating the SSIS catalog is a very simple operation, but it must be performed manually after the installation of SQL Server.
Note that in 2016 you can create custom logging levels for SSIS. I highly highly recommend NOT using any of the pre-built logging levels in production as they all produce a massive amount of logging severely slowing down your SSIS packages. Instead create a custom package and only log everything you need for error events only.
When I right click on Integration Services Catalogs, the Create Catalog is greyed out.
How can I fix this?
https://i.stack.imgur.com/gwFyv.png
When I right-click on Integration Services Catalog in SSMS, the only options are Try SSIS in Azure Data Factory, Reports, and Refresh. What permissions do I need to be able to create the SSISDB? I am able to create a normal database on the instance.
Michael, it may be that the SSIS components are not installed on the server in question. The SSIS runtime and supporting libraries are installed as optional components of the SQL Server installation, and if those components are not selected during the install (they are not selected by default), you won’t be able to create the SSIS catalog on that server.