Working with SQL Server Integration Services Catalogs (SSISDB)

SQL Server Integration services catalogs are very useful for developers and administrator to identify the issues and current state of SSIS Packages.

This feature is available in enterprise and business intelligence editions.

This is one of the key features for SQL Server integration Service (SSIS). 

In order to use the project deployment model is SSIS first SSISDB should be created. If SSISDB is not available it will show the error message as below.

“An Integration Services catalog (SSISDB) was not found on this server instance”

SSISDB must be created using the Integration services catalogs.

A database must not be created with the name SSISDB. It will show the error message if any other user database created with the name SSISDB.

“SSISIDB has been found on the server. But it is not recognized as integration services catalog”.

You must drop or rename any other user database if it contains the database name SSISDB.

SSISDB name is reserved for SSIS catalog.

In order to create SSISDB go to integration services and click on create catalog as shown below.

CLR must be enabled and password must be provided for creating the SSISDB. The password is used for encrypting the SSISDB.

Now SSISDB created successfully.

 Once SSIDB created go to Tables node. Several system tables were created for logging information about SSIS packages.

Some of the important tables are listed here.

[internal].[executions] : Provides the name of the packages that has been executed, project name and in which folder that package is exist in SSISDB and who has executed the SSIS Package.

Internal.folders   : will provide the list folder created in ssisdb catalog

Internal.folder_permissions : list the permission to the folder

 Using the above tables mentioned in the screen shot we can able see the folders ,folder permissions,execution details ,environment and environment variables etc in ssis catalog, 

Using the System stored procedures we can able to create the folder ,folder permissions,environment and data tap for logging ,deleting folder,project deployment etc.

There is also standard reports available to view using the GUI as shown below.

Integration services Dashboard will show the below standard reports

All Executions :

Displays the details of all integration services executions that have performed on server.

All Validations:

Displays validations that have performed on the server

All operations:

Displays the operations that have performed on the server.

.

All Connections :

Displays the connection related information that have applied on server.

We able to view all the packages that have run in the past 24 hours.

We can add custom reports to SSIS dash borad which uses the SSRS rdl files.