The SSIS Catalog or SSISDB is very important to work with the Integration Services projects. It is an essential requirement to deploy packages. Enable this feature during the SQL Server installation or later to use the Integration Services Catalog. This article shows the importance of the Integration Services catalog alias SSISDB and how to create an SSIS catalog and folders.
The SSIS Catalog (SSISDB) is crucial in deploying and managing projects. Within the Management Studio, it creates a central database that stores the projects, packages, package configurations, and execution logs. You can create multiple folders in a database to organize the projects and packages.
SSIS Catalog Introduction
SSIS Catalog creates an environment to upload or store packages, executing and monitoring them. The database administrators use this SSIS Integration Services catalog to execute packages, pass parameters, perform maintenance, and check the logs. There is an Environment folder to handle the configuration settings associated with a package.
We must use the SQL Server Management Studio to enable the SSIS Integration Services Catalog feature. It is the starting point. So, connecting to the Database Engine with proper credentials, who has the rights to create Database rights.
The best part of the SSIS Catalog is that it maintains detailed information on package executions in log files. It includes the package start time, end time, status, duration, parameters used, execution results, data flow statistics, etc.
Steps to Create SSIS Catalog SSISDB
This section explains the step-by-step approach to creating the SSIS Integration Service Catalog with an example. First, open the SQL Server Management Studio to create a catalog. Then, under the Object Explorer window, Please navigate to the Integration Services Catalogs folder, as shown below.
If you or your team member has already created them, they will appear under this folder. If not, right-click on the folder will open the context menu. Please select the Create Catalog.. option to create a new one for integration.
Once you select the option, a new window will open
You must select the Enable CLR Integration option to create or use the SSIS catalog.
Please checkmark the Enable automatic execution of Integration Services stored procedure at SQL Server Startup option and provide the password.
Once you click the OK button, it will start creating the Integration Service catalog.
From the below Management Studio screenshot, you can see that we successfully did.
Let me create a new folder. To do so, right-click on the newly created SSIS Catalog, and select the Create Folder… option from the context menu.
Once you select the Create Folder.. option, a new window will open to create a new folder. Please provide the folder name and a valid description. When you deploy projects from BIDS, you have to select this folder.
Now, you can see the newly created folder. If you observe closely, Integration Services Catalog automatically added the Projects and Environments folder to store the SSIS Packages and variables.
The next step is deploying SSIS projects to the SSISDB by using the BIDS (or Data Tools), SQL Server, and Deployment Wizard to Deploy the Project.
