SSISDB1：使用SSISDB管理SSIS Projects

使用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.

• LOGGING_LEVEL

• DUMP_ON_ERROR

• DUMP_ON_EVENT

• DUMP_EVENT_CODE

• CALLER_INFO

• SYNCHRONIZED

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.

 

参考文档：

SSIS Catalog

Views (Integration Services Catalog)