SSIS Catalog

The SSISDB catalog is the central point for working with Integration Services (SSIS) projects that you’ve deployed to the Integration Services server. For example, you set project and package parameters, configure environments to specify runtime values for packages, execute and troubleshoot packages, and manage Integration Services server operations.

The objects that are stored in the SSISDB catalog include projects, packages, parameters, environments, and operational history.

You inspect objects, settings, and operational data that are stored in the SSISDB catalog, by querying the views in the SSISDB database. You manage the objects by calling stored procedures in the SSISDB database or by using the UI of the SSISDB catalog. In many cases, the same task can be performed in the UI or by calling a stored procedure.

To maintain the SSISDB database, it is recommended that you apply standard enterprise policies for managing user databases. For information about creating maintenance plans, see Maintenance Plans.

The SSISDB catalog and the SSISDB database support Windows PowerShell. For more information about using SQL Server with Windows PowerShell, see SQL Server PowerShell. For examples of how to use Windows PowerShell to complete tasks such as deploying a project, see the blog entry, SSIS and PowerShell in SQL Server 2012, on blogs.msdn.com.

You access the SSISDB catalog in SQL Server Management Studio by connecting to the SQL Server Database Engine and then expanding the Integration Services Catalogs node in Object Explorer. You access the SSISDB database in SQL Server Management Studio by expanding the Databases node in Object Explorer.

Note

You cannot rename the SSISDB database.

Note

If the SQL Server instance that the SSISDB database is attached to, stops or does not respond, the ISServerExec.exe process ends. A message is written to a Windows Event log.

If the SQL Server resources failover as part of a cluster failover, the running packages do not restart. You can use checkpoints to restart packages. For more information, see Restart Packages by Using Checkpoints.

When you create a new object in the catalog, assign a name to the object. The object name is an identifier. SQL Server defines rules for which characters can be used in an identifier. Names for the following objects must follow identifier rules.

Folder

Project

Environment

Parameter

Environment Variable

Folder, Project, Environment

Consider the following rules when renaming a folder, project, or environment.

You fine-tune how the catalog behaves by adjusting the catalog properties. Catalog properties define how sensitive data is encrypted, and how operations and project versioning data is retained. To set catalog properties, use the Catalog Properties dialog box or call the catalog.configure_catalog (SSISDB Database) stored procedure. To view the properties, use the dialog box or query catalog.catalog_properties (SSISDB Database). You access the dialog box by right-clicking SSISDB in Object Explorer.

Operations and Project Version Cleanup

Status data for many of the operations in the catalog is stored in internal database tables. For example, the catalog tracks the status of package executions and project deployments. To maintain the size of the operations data, the SSIS Server Maintenance Job in SQL Server Management Studio is used to remove old data. This SQL Server Agent job is created when Integration Services is installed.

You can update or redeploy an Integration Services project by deploying it with the same name to the same folder in the catalog. By default, each time you redeploy a project, the SSISDB catalog retains the previous version of the project. To maintain the size of the operations data, the SSIS Server Maintenance Job is used to remove old versions of projects.

The job step for project version cleanup runs when this property is set to True.

Maximum Number of Versions per Project

Defines how many versions of a project are stored in the catalog. Older versions of projects are removed.

Encryption Algorithm

The Encryption Algorithm property specifies the type of encryption that is used to encrypt sensitive parameter values. You can choose from the following types of encryption.

AES_256 (default)

AES_192

AES_128

DESX

TRIPLE_DES_3KEY

TRIPLE_DES

DES

When you deploy an Integration Services project to the Integration Servicesserver, the catalog automatically encrypts the package data and sensitive values. The catalog also automatically decrypts the data when you retrieve it. The SSISDB catalog uses the ServerStorage protection level. For more information, see Access Control for Sensitive Data in Packages.

Changing the encryption algorithm is a time-intensive operation. First, the server has to use the previously specified algorithm to decrypt all configuration values. Then, the server has to use the new algorithm to re-encrypt the values. During this time, there cannot be other Integration Services operations on the server. Thus, to enable Integration Services operations to continue uninterrupted, the encryption algorithm is a read-only value in the dialog box in Management Studio.

Projects, environments, and packages are contained in folders that are securable objects. You can grant permissions to a folder, including the MANAGE_OBJECT_PERMISSIONS permission. MANAGE_OBJECT_PERMISSIONS enables you to delegate the administration of folder contents to a user without having to grant the user membership to the ssis_admin role. You can also grant permissions to projects, environments, and operations. Operations include initializing Integration Services, deploying projects, creating and starting executions, validating projects and packages, and configuring the SSISDB catalog.

The SSISDB catalog uses a DDL trigger, ddl_cleanup_object_permissions, to enforce the integrity of permissions information for SSIS securables. The trigger fires when a database principal, such as a database user, database role, or a database application role, is removed from the SSISDB database.

If the principal has granted or denied permissions to other principals, revoke the permissions given by the grantor, before the principal can be removed. Otherwise, an error message is returned when the system tries to remove the principal. The trigger removes all permission records where the database principal is a grantee.

It is recommended that the trigger is not disabled because it ensures that are no orphaned permission records after a database principal is dropped from the SSISDB database.

Managing Permissions

You can manage permissions by using the SQL Server Management Studio UI, stored procedures, and the Microsoft.SqlServer.Management.IntegrationServicesnamespace namespace.

To manage permissions using the SQL Server Management Studio UI, use the following dialog boxes.

A folder contains one or more projects and environments in the SSISDB catalog. You can use the catalog.folders (SSISDB Database) view to access information about folders in the catalog. You can use the following stored procedures to manage folders.

Each project can contain multiple packages. Both projects and packages can contain parameters and references to environments. You can access the parameters and environment references by using the Configure Dialog Box.

You can carry out other project tasks by calling the following stored procedures.

To use the value of a server variable, specify the reference between the project and the server environment. You can use the following stored procedures to create and delete references. You can also indicate whether the environment can be located in the same folder as the project or in a different folder.

To cause a running package to pause and create a dump file, call the catalog.create_execution_dump stored procedure. A dump file provides information about the execution of a package that can help you troubleshoot execution issues. For more information about generating and configuring dump files, see Generating Dump Files for Package Execution.

For details about executions, validations, messages that are logged during operations, and contextual information related to errors, query these views.