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.
For more information about viewing operations data, see Monitoring for Package Executions and Other Operations.
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.
Catalog Object Identifiers
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.
Invalid characters include ASCII/Unicode characters 1 through 31, quote ("), less than (<), greater than (>), pipe (|), backspace (\b), null (\0), and tab (\t).
The name might not contain leading or trailing spaces.
@ is not allowed as the first character, but subsequent characters might use @.
The length of the name must be greater than 0 and less than or equal to 128.
Parameter
Consider the following rules when naming a parameter.
The first character of the name must be a letter as defined in the Unicode Standard 2.0, or an underscore (_).
Subsequent characters can be letters or numbers as defined in the Unicode Standard 2.0, or an underscore (_).
Environment Variable
Consider the following rules when naming an environment variable.
Invalid characters include ASCII/Unicode characters 1 through 31, quote ("), less than (<), greater than (>), pipe (|), backspace (\b), null (\0), and tab (\t).
The name might not contain leading or trailing spaces.
@ is not allowed as the first character, but subsequent characters might use @.
The length of the name must be greater than 0 and less than or equal to 128.
The first character of the name must be a letter as defined in the Unicode Standard 2.0, or an underscore (_).
Subsequent characters can be letters or numbers as defined in the Unicode Standard 2.0, or an underscore (_).
Catalog Configuration
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 following SSISDB catalog properties define how this SQL Server Agent job behaves. You can view and modify the properties by using the Catalog Properties dialog box or by using catalog.catalog_properties (SSISDB Database) and catalog.configure_catalog (SSISDB Database).
Clean Logs Periodically
The job step for operations cleanup runs when this property is set to True.Retention Period (days)
Defines the maximum age of allowable operations data (in days). Older data are removed.The minimum value is one day. The maximum value is limited only by the maximum value of the SQL Server int data. For information about this data type, see int, bigint, smallint, and tinyint (Transact-SQL).
Periodically Remove Old Versions
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.
To change the Encryption Algorithm property setting, set the SSISDB database to the single-user mode and then call the catalog.configure_catalog stored procedure. Use ENCRYPTION_ALGORITHM for the property_name argument. For the supported property values, see catalog.catalog_properties (SSISDB Database). For more information about the stored procedure, see catalog.configure_catalog (SSISDB Database).
For more information about single-user mode, see Set a Database to Single-user Mode. For information about encryption and encryption algorithms in SQL Server, see the topics in the section, SQL Server Encryption.
A database master key is used for the encryption. The key is created when you create the catalog. For more information, see Create the SSIS Catalog.
The following table lists the property names shown in the Catalog Properties dialog box and the corresponding properties in the database view.
Property Name (Catalog Properties dialog box) |
Property Name (database view) |
|---|---|
Encryption Algorithm Name |
ENCRYPTION_ALGORITHM |
Clean Logs Periodically |
OPERATION_CLEANUP_ENABLED​ |
Retention Period (days) |
RETENTION_WINDOW |
Periodically Remove Old Versions |
VERSION_CLEANUP_ENABLED |
Maximum Number of Versions per Project |
MAX_PROJECT_VERSIONS |
Server-wide Default Logging Level |
SERVER_LOGGING_LEVEL |
Permissions
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.
For more information about database roles, see Database-Level Roles.
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.
For a folder, use the Permissions page of the Folder Properties Dialog Box.
For a project, use the Permissions page in the Project Properties Dialog Box.
For an environment, use the Permissions page in the Environment Properties Dialog Box.
To manage permissions using Transact-SQL, call catalog.grant_permission (SSISDB Database), catalog.deny_permission (SSISDB Database) and catalog.revoke_permission (SSISDB Database). To view effective permissions for the current principal for all objects, query catalog.effective_object_permissions (SSISDB Database). This topic provides descriptions of the different types of permissions. To view permissions that have been explicitly assigned to the user, query catalog.explicit_object_permissions (SSISDB Database).
Folders
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.
Projects and Packages
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.
These views provide details about packages, projects, and project versions.
Parameters
You use parameters to assign values to package properties at the time of package execution. To set the value of a package or project parameter and to clear the value, call catalog.set_object_parameter_value (SSISDB Database) and catalog.clear_object_parameter_value (SSISDB Database). To set the value of a parameter for an instance of execution, call catalog.set_execution_parameter_value (SSISDB Database). You can retrieve default parameter values by calling catalog.get_parameter_values (SSISDB Database).
These views show the parameters for all packages and projects, and parameter values that are used for an instance of execution.
Server Environments, Server Variables, and Server Environment References
Server environments contain server variables. The variable values can be used when a package is executed or validated on the Integration Services server.
The following stored procedures enable you to perform many other management tasks for environments and variables.
By calling the catalog.set_environment_variable_protection (SSISDB Database) stored procedure, you can set the sensitivity bit for a variable.
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.
For more details about environments and variables, query these views.
Executions and Validations
An execution is an instance of a package execution. Call catalog.create_execution (SSISDB Database) and catalog.start_execution (SSISDB Database) to create and start an execution. To stop an execution or a package/project validation, call catalog.stop_operation (SSISDB Database).
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.
You can validate projects and packages by calling the catalog.validate_project (SSISDB Database) and catalog.validate_package (SSISDB Database) stored procedures. The catalog.validations (SSISDB Database) view provides details about validations such as the server environment references that are considered in the validation, whether it is a dependency validation or a full validation, and whether the 32-bit runtime or the 64-bit runtime is used to run the package.
Related Tasks
Related Content
Blog entry, SSIS and PowerShell in SQL Server 2012, on blogs.msdn.com.
Blog entry, SSIS Catalog Access Control Tips, on blogs.msdn.com.
Blog entry, A Glimpse of the SSIS Catalog Managed Object Model, on blogs.msdn.com.
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services