In this article, I am going to explain in detail the SSIS catalog that can be used to deploy SQL Server Integration Services (SSIS) projects. Using this catalog, developers and database administrators can easily deploy and manage their integration services projects after deployment. 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.
Figure 2 – SSISDB created under the Integration Services Catalog
Integration Services Catalog Properties
Now that the catalog database has been created, let us go ahead and understand the properties of the database. By default, the following configuration will be set for the catalog database, but it can be altered based on the requirements.
Right-click on the SSISDB folder and select Properties. The properties dialog box will open up as follows.
![SSISDB Database properties](/wp-content/uploads/2020/07/ssisdb-database-properties.png")
Figure 3 – SSISDB Database properties
The main properties that need to be taken care of by the database administrators are as follows:
- Clean Logs Periodically – This is a Boolean property, which if set to TRUE will clean the execution logs of the packages based on the Retention Period
- Retention Period (Days) – This is the number of days after which the logs can be cleaned up from the SSISDB database
- Server-wide Default Logging Level – This value is used to set the default logging level for the packages if those are set to None during development. There are few different levels of logging starting from the Basic, Performance, Verbose and Runtime Lineage
- Maximum Number of Versions per Project – As it goes by the name, this property holds value for the maximum number of versions of the packages that can be retained by the catalog database. This is helpful if you want to revert to certain versions after a new version has been deployed
- Periodically Remove Old Versions – This is also a Boolean value, which if set to TRUE will keep the maximum number of versions with respect to the above property and will remove the older versions from the catalog database
Understanding catalog objects
Once the catalog database has been created, it is important to understand the architecture of the database and the various objects within it. The following diagram somewhat explains the architecture of the catalog database and how it relates to the SQL Server database engine.
Figure 4 – SSIS Catalog Database architecture
If you see in the diagram, the top-level component here is the SQL Server Database Engine, on which the Integration Services Catalog database resides. Inside the Catalog database, you can organize your SSIS projects in folders. This is just a logical grouping of folders that you can use to group your projects. Usually, I create folders specific to different customers and then deploy the projects within those folders.
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.
Figure 6 – SSIS Catalog Database Structure
Deploying the projects
Now that we have some idea about the Integration Services Catalog database structure, let us now understand how we can deploy the SSIS projects and packages to this catalog database after development. Basically, there are three modes of project deployment into the catalog database as follows.
- Using SQL Server Management Studio
- Using the SQL Server Data Tools / Visual Studio
- Using TSQL scripts
Well, as you might have already guessed, the most preferred way of deploying the projects would be by using the SQL Server Management Studio. It provides a graphical user interface of carrying out all the necessary actions required to deploy an Integration Services project. The final build of an SSIS project is an ISPAC file which contains all the packages within and can be used as a deployment file.
However, during the development phase, you might need to frequently need to redeploy the projects to verify certain features. In that case, you can deploy the project directly from the SQL Server Data Tools (SSDT) or Visual Studio. This helps the developers directly deploy the projects into the server without having to leave the development environment.
Finally, another way to deploy SSIS packages to the SSIS catalog would be to use the T-SQL scripts. Using this feature gives the database administrators greater control over the deployment and configuration part and these scripts can also be used to automate certain deployments if required.
Conclusion
In this article, I have explained the SSIS Catalog database (SSISDB) that is created within the SQL Server instance. This catalog database is one of the most important concepts that need to be understood while dealing with SSIS project deployments. Using the catalog database, you can easily configure parameters, set environments, and manage other activities. To learn more about the catalog database, please refer to the official documentation from Microsoft.
He is a prolific author, with over 100 articles published on various technical blogs, including his own blog, and a frequent contributor to different technical forums.
In his leisure time, he enjoys amateur photography mostly street imagery and still life. Some glimpses of his work can be found on Instagram. You can also find him on LinkedIn
View all posts by Aveek Das
- Getting started with PostgreSQL on Docker - August 12, 2022
- Getting started with Spatial Data in PostgreSQL - January 13, 2022
- An overview of Power BI Incremental Refresh - December 6, 2021