Can not create Catalog (SSISDB) due to an error

ssis catalog

Introduction

The SSISDB database (a.k.a. the SQL Server Integration Services catalog) was introduced back in SQL Server 2012 as a way to de-clutter the MSDB database and provide some built-in logging and reporting infrastructure. In a nutshell, SSISDB is an SSIS framework making SQL Server Integration Services more robust and enterprise-friendly by providing the following features:

  • Database backup
  • Database encryption
  • Support for environments
  • Support project and package parameters
  • Package versioning
  • Custom SSRS performance reports built into SSMS
  • Deployment directly from SSDT

Fortunately or unfortunately, installation of a new SQL Server instance does not include the creation of the SSISDB Catalog, even if SQL Server Integration Services is included in the installation. For some reason, Microsoft decided to leave that part to the user (luckily, this is a one-time sunk cost).

Once SQL Server installation is completed, you would need to create Catalog using SSMS (see Fig #1) by expanding Management, right clicking on Integration Services Catalogs, and clicking on Create Catalog menu item (see Figure 1).

ssis catalog
Figure 1 – Create Catalog

Problem

Once you are done with the full SQL Server installation and ready to create Catalog (SSISDB Catalog to be exact), you might be “greeted” with the following error – “The catalog backup file 'C:\Program Files\Microsoft SQL Server\140\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)“.

I’m sure you were surprised as I am! What? Why would creation of SSISDB Catalog have anything to do with SSISDB database backup (see Fig #2)? Apparently it’s one of the internal steps that Microsoft does behind the scenes when you are installing SQL Server. To be absolutely specific, installation of an Integration Services feature. Strange, right? The real problem is that when you are trying to create an SSISDB Catalog, Microsoft “assumes” that there is database backup file (see Figure 2).

Figure 2 – SSISDBBackup.bak

Solution

Apparently, if you forget to enable installation of Integration Services feature, this is the message that you are going to see. The solution involves going back to the installation (see Figure 3) and checking the Integration Services feature.

Figure 3 – SQL Server Installation Center
SQL Server Integration Services
Figure 4 – Integration Services

Once you have Integration Services enabled (see Figure 4) and the installation completed, you should be able to Create Catalog without any problem.

Disclaimer

This blog post is based on the following forum discussion https://www.sqlservercentral.com/forums/topic/missing-ssisdbbackup-bak.

Please share this

This Post Has One Comment

  1. tomas

    Exactly what I needed to know. Thanks!

Leave a Reply

Related Articles