使用Project Deployment Model，将SSIS Project部署到Integration Services Catalog之后，SSISDB负责管理SSIS Project。在SSISDB中，有两个schema：internal和catalog。Internal 框架下的object是内部使用的，catalog框架下的object是供user使用的。在SSISDB中，能够使用TSQL和package交互，比如，使用视图查看package的执行信息，调用stored procedure执行package，根据package的history message对package进行troubleshoot，管理package执行的参数或环境变量。
一，Catalog objects
1，Catalog的object 有Folder，Project，Package ，Executable 和 Parameter
- 使用 [catalog].[folders] 查看Folder
- 使用 [catalog].[projects] 查看Project
- 使用 [catalog].[packages] 查看Package
- 使用 [catalog].[executables] 和 [catalog].[executable_statistics] 查看Executable
- 使用 [catalog].[object_parameters] 和 [catalog].[execution_parameter_values] 查看 Parameter
Executable 是package的可执行组件，位于Control flow tab中，是一个Task 或者 Container。
An executable is a task or container that you add to the control flow of a package.
2，参数
[catalog].[object_parameters] ：Displays the parameters for all packages and projects in the Integration Services catalog.
关键的字段：
|
Column name |
Data type |
Description |
|---|---|---|
|
parameter_id |
bigint |
The unique identifier (ID) of the parameter. |
|
project_id |
bigint |
The unique ID of the project. |
|
object_type |
smallint |
The type of parameter. The value is 20 for a project parameter and the value is 30 for a package parameter. |
|
object_name |
sysname |
The name of the corresponding project or package. |
|
parameter_name |
sysname |
The name of the parameter. |
|
data_type |
nvarchar(128) |
The data type of the parameter. |
|
required |
bit |
When the value is 1, the parameter value is required in order to start the execution. When the value is 0, the parameter value is not required to start the execution. |
|
sensitive |
bit |
When the value is 1, the parameter value is sensitive. When the value is 0, the parameter value is not sensitive. |
|
description |
nvarchar(1024) |
An optional description of the package. |
|
design_default_value |
sql_variant |
The default value for the parameter that was assigned during the design of the project or package. |
|
default_value |
sql_variant |
The default value that is currently used on the server. |
|
value_type |
char(1) |
Indicates the type of parameter value. This field displays V when parameter_value is a literal value and R when the value is assigned by referencing an environment variable. |
|
value_set |
bit |
When the value is 1, the parameter value has been assigned. When the value is 0, the parameter value has not been assigned. |
|
referenced_variable_name |
nvarchar(128) |
The name of the environment variable that is assigned to the value of the parameter. The default value is NULL. |
2.1， object_type：The value is 20 for a project parameter and the value is 30 for a package parameter.
2.2，一个parameter 有三种value： design_default_value，default_value， 和 execution_value，
Design Default Value 是指在BIDT中Design package时指定的默认值
Default Value 是指Parameter 部署到IS中的默认值，如果没有修改Parameter的value，那么Default value 就是Design Default Value；当然，可以在SSMS中修改成一个和Data Type 兼容的值，可以和Design Default Value 不同。
Execution Value是指Parameter 是指 package 运行时Parameter 的值，由于在execute package，可以选择设置Parameter的Value 或 选择使用Default Value，所以 Execution Value 就是Package 的一次execution所使用的parameter value。
2.3， value_type
This field displays V when parameter_value is a literal value and R when the value is assigned by referencing an environment variable.
catalog.execution_parameter_values ：Displays the actual parameter values that are used by Integration Services packages during an instance of execution.
|
Column name |
Data type |
Description |
|---|---|---|
|
execution_parameter_id |
bigint |
Unique identifier (ID) of the execution parameter. |
|
execution_id |
bigint |
Unique ID for the instance of execution. |
|
object_type |
smallint |
When the value is 20, the parameter is a project parameter. When the value is 30, the parameter is a package parameter. When the value is 50, the parameter is one of the following.
|
|
parameter_data_type |
nvarchar(128) |
The data type of the parameter. |
|
parameter_name |
sysname |
The name of the parameter. |
|
parameter_value |
sql_variant |
The value of the parameter. When sensitive is 0, the plaintext value is shown. When sensitive is 1, the NULL value is displayed. |
|
sensitive |
bit |
When the value is 1, the parameter value is sensitive. When the value is 0, the parameter value is not sensitive. |
|
required |
bit |
When the value is 1, the parameter value is required in order to start the execution. When the value is 0, the parameter value is not required to start the execution. |
|
value_set |
bit |
When the value is 1, the parameter value has been assigned. When the value is 0, the parameter value has not been assigned. |
|
runtime_override |
bit |
When the value is 1, the parameter value was changed from the original value before the execution was started. When the value is 0, the parameter value is the original value that was set. |
二，Catalog Configuration
2.1, 使用 [catalog].[catalog_properties] 查看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 |
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.
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.
2.2, 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.
参考文档：