In Microsoft SQL Server Code-Named “Denali” CTP1, the SSISDB catalog is the central storage and administration point for Integration Services (SSIS) projects, packages, parameters, and environments. There is only one catalog on an instance of SQL Server. The catalog and other new features in SQL Server Code-Named “Denali” CTP1 are an evolution of Integration Services deployment and administration in SQL Server. This topic introduces the catalog, describes how to create and view it in SQL Server Management Studio, and provides a detailed look at catalog properties.
Note: Parameters and the Integration Services catalog are available only to projects using the project deployment model. For more information about the project deployment model, see Integration Services (SSIS) Project Deployment Overview.
In the catalog, projects and environments are stored in folders. A folder is a securable object and is designed so that you can delegate the administration of folder contents to other users without having to grant them administrator permissions. To delegate folder administration to another user in the SSISDB database, grant the user the MANAGE_OBJECT_PERMISSIONS permission on the folder. For more information about security, see Integration Services (SSIS) Security in SQL Server.
When you first install the SQL Server Code-Named "Denali" CTP1 database engine, the Integration Services catalog does not exist. The catalog uses CLR stored procedures, so you will need to enable CLR integration on the database engine where it will reside, if CLR integration is not enabled already. For more information, see Enabling CLR Integration in the MSDN Library. After CLR integration is enabled, you can use SQL Server Management Studio Object Explorer to create the catalog. As shown in the following image, right-click the Integration Services node and then click Create Catalog.
In the Create Catalog dialog box you will be asked to supply a password. This password is used to create a database master key. The database master key is used to encrypt sensitive data in the catalog. After the catalog has been created, we highly recommend that you back up the database master key. For more information, see How to: Back Up a Database Master Key in the MSDN Library. The following image shows the SQL Server Code-Named "Denali" CTP1 Create Catalog dialog box.
SQL Server Management Studio Object Explorer displays the catalog twice, first in the Databases node and then in the Integration Services node. In the Databases node, you can work with the catalog, SSISDB, like any other user database. In the Integration Services node, you can administer the catalog by right-clicking the corresponding child nodes.
In the following image, the red arrow shows SSISDB in the Databases node and the blue arrow shows SSISDB in the Integration Services node.
You can fine-tune how the catalog behaves by adjusting the catalog properties. Catalog properties define how sensitive data is encrypted, how operations and project versioning data is retained, and when validation operations time out. With Transact-SQL, you can view catalog properties with the catalog.catalog_property view and set them with the catalog.configure_catalog stored procedure. In SQL Server Management Studio Object Browser, you can right-click the catalog, SSISDB, under the Integration Services node and then click Properties. Note that the property names are slightly different between Transact-SQL and the catalog properties dialog box. The following table compares the property names.
Note: The following text refers to the properties by their Transact-SQL property names.
All parameters have the option of being marked as sensitive. When a parameter is sensitive, its values are encrypted before they are stored in the catalog. When sensitive parameter values are queried with Transact-SQL or viewed in SQL Server Management Studio, a NULL value is displayed instead of the decrypted sensitive parameter value. The ENCRYPTION_ALGORITHM property defines what type of encryption is used to encrypt the sensitive parameter values in the catalog. In SQL Server Code-Named "Denali" CTP1, you can choose from one of seven encryption technologies:
Note: To change the ENCRYPTION_ALGORITHM property, you must first put the catalog in single-user mode.
Status data for many of the operations in the catalog is stored in internal database tables. For example, the catalog keeps track of operations status for package executions and project deployments (to see the complete list, refer to the catalog.operations Transact-SQL view documentation). To maintain the size of the operations data, a SQL Agent job is used to remove old data. The following two properties define how this job behaves:
You can view catalog operations data with the catalog.operations, catalog.extended_operations_info and catalog.operations_messages Transact-SQL views. In SQL Server Management Studio Object Explorer, you can view the catalog operations by right-clicking SSISDB under the Integration Services node and then clicking Operations.
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 catalog retains the previous version of the project. To maintain the size of the operations data, a SQL Agent job is used to remove old versions of projects. The following two catalog properties define how this job behaves:
You can view versioning information for all projects in the catalog with the catalog.object_versions Transact-SQL view. To view versioning information for a specific project in SQL Server Management Studio Object Explorer, right-click the project in the Integration Services node and then click Versions.
Performing project validation and package validation is an asynchronous operation. The larger the project or package is, the longer it will take to validate. To limit the amount of time a validation can run, you can use the VALIDATION_TIMEOUT catalog property. The VALIDATION_TIMEOUT property specifies the maximum number of seconds a validation can run before it is stopped. By default, a validation has 300 seconds to finish processing before it is stopped. For more information, see Integration Services (SSIS) Project and Package Validation.
Use the catalog.validate_project and catalog.validate_package stored procedures to start a project and package validation, respectively. When you execute these stored procedures, each returns the corresponding validation ID with an output parameter named validation_id. To monitor the status of the validation, you can use that ID to selectively query the catalog.operations, catalog.validations, catalog.extended_operations_info, and catalog.operation_messages views. To validate a project or package with the SQL Server Management Studio Object Explorer, right-click the project or package and then click Validate. Then, right-click the Integration Services node and click Operations to see the validation operations details.
Integration Services (SSIS) Project Deployment Overview Integration Services (SSIS) Catalog Overview Integration Services (SSIS) Catalog Architecture and API Integration Services (SSIS) Projects Integration Services (SSIS) Parameters Integration Services (SSIS) Environment Variables Integration Services (SSIS) Package Execution Integration Services (SSIS) Project and Package Validation Integration Services (SSIS) Security in SQL Server Monitoring Operations in the Integration Services (SSIS) Catalog Integration Services (SSIS) Catalog Identifiers