SSIS 2016 Administration: Create the SSIS Catalog

Creating an instance of the SSIS 2016 Catalog is identical to creating an SSIS Catalog in SQL Server 2012 or SQL Server 2014. Follow these steps to create an instance of the SSIS Catalog on SQL Server 2016.

Open SQL Server Management Studio (SSMS) and connect to an instance of SQL Server. Once connected, open Object Explorer (if it is not already open) and right-click the Integration Services Catalogs node. Click Create Catalog…:

When the Create Catalog window displays (for the first time) it will appear as shown here:

Check the “Enable CLR Integration” checkbox to enable the other controls on the form.

I recommend you also check the “Enable automatic execution of Integration Services stored procedure at SQL Server startup” checkbox. This feature causes a stored procedure to execute whenever SQL Server starts. The stored procedure will identify any SSIS packages in a running (or other “active”) status and mark them as “Ended Unexpectedly.” You want this. Trust me. (As my friend Kevin Boles (LinkedIn | @thesqlguru) says, “Push the trust me button and let’s move on,” (paraphrased).

You cannot alter the name of the SSIS Catalog database. It is SSISDB. And, as in Highlander, there can be only one SSIS Catalog per instance of SQL Server.

You must supply a password for encryption used in the SSISDB database. The SSIS Catalog encrypts SSIS packages using a rough equivalent of what we used to call “Server Storage” package protection.

<throwback> Using Server Storage encryption was perhaps the most (perhaps the only) valid use case for deploying SSIS packages to the MSDB database, back in the day.</throwback>

The SSIS Catalog encrypts passwords and values marked Sensitive (such as SSIS Package and SSIS Project parameters).

SQL Server 2016 requires you supply a strong password for the SSISDB database. This is a default in SQL Server 2016. Even if I knew how to defeat it, I wouldn’t tell you. It’s 2016 people. Use strong passwords or risk being pwn’d by the bad guys!

Once configured, the Create Catalog window will appear as shown below. Click the OK button to create an SSIS Catalog:

What happens when you press the OK button? The SSISDB database is restored. Since I deployed SQL Server 2016 to the E: drive on my Windows Server 2016 Preview 5 virtual machine, the backup file is located at E:\Program Files\Microsoft SQL Server\130\DTS\Binn\SSISDBBackup.bak for me.

The SSISDB Catalog node will appear under the Integration Services Catalogs node in Object Explorer.

We’re not there yet, but you can find helpful utility applications for Enterprise Data Integration at DILMSuite.com (Data Integration Lifecycle Management Suite).

You will need to refresh the Databases nod in Object Explorer to see the SSISDB database:

What’s in the SSISDB database? Well, there are tables…

… views…

… and stored procedures…

… and functions and other database-y stuff. It’s just a database. That’s important to remember. It needs the same care and feeding as the other databases in your enterprise. It’s a neat piece of work, but it’s still just a database. It may not scale well for you in your enterprise. You may need to tune it. You definitely need to back it up. You need to store the encryption keys somewhere, preferably somewhere safe and not on the same physical server. Restoring SSISDB needs to be part of your disaster recovery (DR) planning and DR tests.

You are doing DR tests, aren’t you? Regularly?

I hope this post helps you get an SSIS Catalog up and running in SQL Server 2016. If you have any questions, please leave them in the (moderated) comments below or email me using the link in the upper right section of this page.

Enterprise Data & Analytics can help you put SSIS, data warehousing, business intelligence, and analytics to work for your enterprise. We offer: