SSISDB 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 a variety of 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 Operations Monitoring for the Integration Services Server.
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 restart packages using checkpoints. For more information, see Restart Packages by Using Checkpoints |
When you create a new object in the catalog, you must 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 may not contain leading or trailing spaces.
-
@ is not allowed as the first character, but subsequent characters may 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 may not contain leading or trailing spaces.
-
@ is not allowed as the first character, but subsequent characters may 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 (_).
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, right-click the SSISDB catalog and then click Properties, or call the catalog.configure_catalog (SSISDB Database) stored procedure. You can inspect properties settings by querying the catalog.catalog_properties (SSISDB Database) view.
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
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. For more information about single-user mode, see Set a Database to Single-user Mode.
A database master key is used for the encryption. The key is created when you create the catalog. For more information, see Create the SSISDB 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 |
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 keeps track of operations status for 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.
The following two SSISDB catalog properties define how this SQL Server Agent job behaves.
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, a SQL Server Agent job is used to remove old versions of projects. The following two SSISDB catalog properties define how this job behaves.
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. The MANAGE_OBJECT_PERMISSIONS permission enables you to delegate the administration of folder contents to a user without having to grant the user membership to the ssis_admin role. For more information about database roles, see Database-Level Roles.
To grant permissions to a folder, use the Permissions page of the Folder Properties Dialog Box or call the catalog.grant_permission stored procedure. For more information on the stored procedure and the permissions you can assign to users, see catalog.grant_permission (SSISDB Database).
You can grant users certain permissions to individual projects and environments, and to operations. Operations include initializing Integration Services, deploying projects, creating and starting executions, validating projects and packages, and configuring the SSISDB catalog. To grant permissions to a project or an environment, use the Permissions page in the Project Properties Dialog Box or the Environment Properties Dialog Box, or call the catalog.grant_permission stored procedure. To grant permissions to an operation, call the stored procedure.
You can also deny and revoke permissions by calling the catalog.deny_permission (SSISDB Database) and catalog.revoke_permission (SSISDB Database) stored procedures.
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).
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, you need to 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 will remove all permission records where the database principal is a grantee.
It is recommended that the trigger not be disabled because it ensures that are no orphaned permission records after a database principal is dropped from the SSISDB database.
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 deploy, move and carry out other project admin tasks by calling the following stored procedures.
These views provide details about packages, projects, and project versions.
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 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 create, delete, move and 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, you need to specify the reference between the project and the server environment. You can use the following stored procedures to create and delete references, and to 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.
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.
-
Blog entry, SSIS and PowerShell in SQL Server 2012, on blogs.msdn.com.
-
Blog entry, SSIS Catalog Access Control Tips, on blogs.msdn.com.
|
