Introduction to the SSIS Catalog database (SSISDB)

The SSIS Catalog database was introduced in SQL Server 2012 and prior to that users had the following three options to deploy their SSIS packages:

• Users could deploy the packages to a directory on the file system
• Users could deploy the packages to the package store which ultimately stores the packages on the file system but inside the SQL Server directory instead
• Finally, users could deploy the packages to the SQL Server instance where the packages were stored within the MSDB database in the “sysssispackages” table

Creating the SSIS Catalog

In order to create the SSIS catalog database, you need to have SQL Server already up and running on the machine, to begin with. This catalog database is just another simple database object which helps us store the SSIS packages in a more organized way.

To create this catalog database, right-click on the “Integration Services Catalog” option and select “Create Catalog…”. In the Create Catalog dialog box that opens up, enable the checkbox for “Enable CLR Integration” and provide a password for the catalog database. You will usually not require this password to use the SSIS objects within the catalog database.

Figure 1 – Create the Catalog Database using SQL Server Management Studio

I have already enabled the catalog database in my machine, so I will not be able to share the screenprints at the moment. However, once you are done with setting the password, hit OK and that will be it. The new catalog database will be created, and you can see it in the following screenshot.

Now, every folder in the catalog database is capable of one or multiple projects. As soon as you create a new folder, you can automatically see two subfolders being created within it – one for the Projects and one for the Environments.

Figure 5 – SSISDB Folder Structure

The Projects folder contains the SSIS project that you deploy to the SSIS Catalog database which further contains the individual DTSX packages along with the following objects.

• SSIS Project and Package Parameters – If any project-level parameters were used in the project then you can configure those parameters in these settings
• Environment References – This object is used to set the environment on which the packages are to be executed. You can configure multiple environments on the same machine and then just change the reference to use the desired environment as required
• Packages – All the packages that were designed in the project will be listed under this directory. You can also configure custom parameters for each of the packages separately

The Environments folder consists of the various environments that can be used for executing the SSIS packages (development, QA, staging, production etc.). You can create multiple environments, one for each as mentioned and then define the Environment Variables within each of them. These variables can contain connection strings for different databases, or other project or package parameter values based on the environment. This can be referred to as an alternate option for setting up configuration files.

Below is an example of how the entire SSIS catalog structure might look like.

Sources:

Introduction to the SSIS Catalog database (SSISDB)