监视运行包和其他操作Monitor Running Packages and Other Operations

适用对象:是SQL Server,包含 Linux 版 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server, including on Linux yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

您可以使用以下一个或多个工具监视 Integration ServicesIntegration Services 包执行、项目验证和其他操作。You can monitor Integration ServicesIntegration Services package executions, project validations, and other operations by using one of more of the following tools. 某些工具(如数据分流)只适用于部署到 Integration ServicesIntegration Services 服务器的项目。Certain tools such as data taps are available only for projects that are deployed to the Integration ServicesIntegration Services server.

备注

本文介绍如何在一般情况下监视正在运行的 SSIS 包以及如何在本地监视正在运行的包。This article describes how to monitor running SSIS packages in general, and how to monitor running packages on premises. 还可运行和监视 Azure SQL 数据库中的 SSIS 包。You can also run and monitor SSIS packages in Azure SQL Database. 有关详细信息,请参阅将 SQL Server Integration Services 工作负荷直接迁移到云For more info, see Lift and shift SQL Server Integration Services workloads to the cloud.

虽然也可以在 Linux 上运行 SSIS 包,但 Linux 上不提供任何监视工具。Although you can also run SSIS packages on Linux, no monitoring tools are provided on Linux. 有关详细信息,请参阅使用 SSIS 在 Linux 上提取、转换和加载数据For more info, see Extract, transform, and load data on Linux with SSIS.

操作类型Operation Types

服务器的 SSISDB Integration ServicesIntegration Services 目录中监视几种不同的操作类型。Several different types of operations are monitored in the SSISDB catalog, on the Integration ServicesIntegration Services server. 每个操作可以具有多个与其关联的消息。Each operation can have multiple messages associated with it. 每个消息可划分为若干不同类型之一。Each message can be classified into one of several different types. 例如,消息可以是信息、警告或错误类型。For example, a message can be of type Information, Warning, or Error. 有关消息类型的完整列表,请参阅针对 Transact-SQL catalog.operation_messages(SSISDB 数据库)视图的文档。For the full list of message types, see the documentation for the Transact-SQL catalog.operation_messages (SSISDB Database) view. 有关操作类型的完整列表,请参阅 catalog.operations(SSISDB 数据库)For a full list of the operations types, see catalog.operations (SSISDB Database).

使用九种不同的状态类型来指示操作的状态。Nine different status types are used to indicate the status of an operation. 有关状态类型的完整列表,请参阅 catalog.operations(SSISDB 数据库)视图。For a full list of the status types, see the catalog.operations (SSISDB Database) view.

“活动操作”对话框Active Operations Dialog Box

使用 “活动操作” 对话框可以查看 Integration ServicesIntegration Services 服务器上当前运行的 Integration ServicesIntegration Services 操作的状态,例如部署、验证和包执行。Use the Active Operations dialog box to view the status of currently running Integration ServicesIntegration Services operations on the Integration ServicesIntegration Services server, such as deployment, validation, and package execution. 此数据存储在 SSISDB 目录中。This data is stored in the SSISDB catalog.

有关相关 Transact-SQLTransact-SQL 视图的详细信息,请参阅 catalog.operations(SSISDB 数据库)catalog.validations(SSISDB 数据库)catalog.executions(SSISDB 数据库)For more information about related Transact-SQLTransact-SQL views, see catalog.operations (SSISDB Database), catalog.validations (SSISDB Database), and catalog.executions (SSISDB Database)

打开“活动操作”对话框Open the Active Operations Dialog Box

  1. 打开 SQL ServerSQL ServerManagement StudioManagement StudioOpen SQL ServerSQL ServerManagement StudioManagement Studio.

  2. 连接 Microsoft SQL Server 数据库引擎Connect Microsoft SQL Server Database Engine

  3. 在对象资源管理器中,展开 Integration Services 节点,右键单击 SSISDB,然后单击 “活动操作”In Object Explorer, expand the Integration Services node, right-click SSISDB, and then click Active Operations.

配置选项Configure the Options

类型Type
指定操作的类型。Specifies the type of operation. 下面是 “类型” 字段的可能值以及 Transact-SQL catalog.operations 视图的 operations_type 列中的相应值。The following are the possible values for the Type field and the corresponding values in the operations_type column of the Transact-SQL catalog.operations view.

Integration Services 初始化Integration Services initialization 11
操作清除(SQL 代理作业)Operations cleanup (SQL Agent job) 22
项目版本清除(SQL 代理作业)Project versions cleanup (SQL Agent job) 33
部署项目Deploy project 101101
还原项目Restore project 106106
创建和启动包执行Create and start package execution 200200
停止操作(停止验证或执行)Stop operation (stopping a validation or execution 202202
验证项目Validate project 300300
验证包Validate package 301301
配置目录Configure catalog 10001000

停止Stop
单击以停止当前正在运行的操作。Click to stop a currently running operation.

查看和停止在 Integration Services 服务器上运行的包Viewing and Stopping Packages Running on the Integration Services Server

SSISDB 数据库在对用户不可见的内部表中存储执行历史记录。The SSISDB database stores execution history in internal tables that are not visible to users. 不过,它通过您可以查询的公共视图公开您所需的信息。However it exposes the information that you need through public views that you can query. 它还提供存储过程,您可以调用这些存储过程以执行与包相关的常见任务。It also provides stored procedures that you can call to perform common tasks related to packages.

通常,您在 Integration ServicesIntegration Services 中的服务器上管理 SQL Server Management StudioSQL Server Management Studio对象。Typically you manage Integration ServicesIntegration Services objects on the server in SQL Server Management StudioSQL Server Management Studio. 不过,您还可以查询数据库视图和直接调用存储过程,或者编写调用托管 API 的自定义代码。However you can also query the database views and call the stored procedures directly, or write custom code that calls the managed API. SQL Server Management StudioSQL Server Management Studio 和托管 API 查询视图并调用存储过程以便执行其许多任务。and the managed API query the views and call the stored procedures to perform many of their tasks. 例如,您可以查看当前正在服务器上运行的 Integration ServicesIntegration Services 包的列表,并且在需要时请求包停止运行。For example, you can view the list of Integration ServicesIntegration Services packages that are currently running on the server, and request packages to stop if you have to.

查看正在运行的包的列表Viewing the List of Running Packages

您可以在 “活动操作” 对话框中查看当前正在服务器上运行的包的列表。You can view the list of packages that are currently running on the server in the Active Operations dialog box. 有关详细信息,请参阅 Active Operations Dialog BoxFor more information, see Active Operations Dialog Box.

有关可用于查看正在运行的包列表的其他方法的信息,请参阅以下主题。For information about the other methods that you can use to view the list of running packages, see the following topics.

Transact-SQLTransact-SQL accessaccess
若要查看正在服务器上运行的包的列表,请为其状态为 2 的包查询视图 catalog.executions(SSISDB 数据库)To view the list of packages that are running on the server, query the view, catalog.executions (SSISDB Database) for packages that have a status of 2.

通过托管 API 以编程方式访问Programmatic access through the managed API
请参阅 Microsoft.SqlServer.Management.IntegrationServices 命名空间及其类。See the Microsoft.SqlServer.Management.IntegrationServices namespace and its classes.

停止正在运行的包Stopping a Running Package

您可以在 “活动操作” 对话框中请求正在运行的包停止。You can request a running package to stop in the Active Operations dialog box. 有关详细信息,请参阅 Active Operations Dialog BoxFor more information, see Active Operations Dialog Box.

有关可用于停止正在运行的包的其他方法的信息,请参阅以下主题。For information about the other methods that you can use to stop a running package, see the following topics.

Transact-SQLTransact-SQL accessaccess
若要停止正在服务器上运行的包,请调用存储过程 catalog.stop_operation(SSISDB 数据库)To stop a package that is running on the server, call the stored procedure, catalog.stop_operation (SSISDB Database).

通过托管 API 以编程方式访问Programmatic access through the managed API
请参阅 Microsoft.SqlServer.Management.IntegrationServices 命名空间及其类。See the Microsoft.SqlServer.Management.IntegrationServices namespace and its classes.

查看已运行的包的历史记录Viewing the History of Packages That Have Run

若要查看在 Management StudioManagement Studio中已运行的包的历史记录,请使用 “全部执行” 报表。To view the history of packages that have run in Management StudioManagement Studio, use the All Executions report. 有关“全部执行” 报表和其他标准报表的详细信息,请参阅 Integration Services 服务器的报告For more information on the All Executions report and other standard reports, see Reports for the Integration Services Server.

有关可用于查看正在运行的包的历史记录的其他方法的信息,请参阅以下主题。For information about the other methods that you can use to view the history of running packages, see the following topics.

Transact-SQLTransact-SQL accessaccess
若要查看与已运行的包有关的信息,请查询视图 catalog.executions(SSISDB 数据库)To view information about packages that have run, query the view, catalog.executions (SSISDB Database).

通过托管 API 以编程方式访问Programmatic access through the managed API
请参阅 Microsoft.SqlServer.Management.IntegrationServices 命名空间及其类。See the Microsoft.SqlServer.Management.IntegrationServices namespace and its classes.

Reports for the Integration Services ServerReports for the Integration Services Server

在当前版本的 SQL ServerSQL ServerIntegration ServicesIntegration Services中, SQL Server Management StudioSQL Server Management Studio 提供了标准报告,帮助你监视部署到 Integration ServicesIntegration Services 服务器的 Integration ServicesIntegration Services 项目。In the current release of SQL ServerSQL ServerIntegration ServicesIntegration Services, standard reports are available in SQL Server Management StudioSQL Server Management Studio to help you monitor Integration ServicesIntegration Services projects that have been deployed to the Integration ServicesIntegration Services server. 这些报告有助于您查看包状态和历史记录,并根据需要确定包执行失败的原因。These reports help you to view package status and history, and, if necessary, identify the cause of package execution failures.

在每个报告页的顶部,后退图标会将您转到查看过的上一页,刷新图标会刷新在该页上显示的信息,打印图标则可以打印当前页。At the top of each report page, the back icon takes you to the previous page you viewed, the refresh icon refreshes the information displayed on the page, and the print icon allows you to print the current page.

有关如何将包部署到 Integration ServicesIntegration Services 服务器的详细信息,请参阅部署 Integration Services (SSIS) 项目和包For information on how to deploy packages to the Integration ServicesIntegration Services server, see Deploy Integration Services (SSIS) Projects and Packages.

Integration Services 面板Integration Services Dashboard

“Integration Services 面板” 报告提供 SQL ServerSQL Server 实例上所有包执行的概览。The Integration Services Dashboard report provides an overview of all the package executions on the SQL ServerSQL Server instance. 对于已在服务器上运行的每个包,该面板允许您“放大”以便查找有关可能已发生的包执行错误的具体细节。For each package that has run on the server, the dashboard allows you to "zoom in" to find specific details on package execution errors that may have occurred.

该报告显示以下信息部分。The report displays the following sections of information.

部分Section 描述Description
执行信息Execution Information 显示过去 24 小时内处于不同状态(失败、正在运行、成功、其他)的执行次数。Shows the number of executions that are in different states (failed, running, succeeded, others) in the past 24 hours.
包信息Package Information 显示过去 24 小时内执行的包总数。Shows the total number of packages that have been executed in the past 24 hours.
连接信息Connection Information 显示过去 24 小时内失败的执行中使用的连接。Shows the connections that have been used in failed executions in the past 24 hours.
包详细信息Package Detailed Information 显示过去 24 小时内发生的已完成执行的详细信息。Shows the details of the completed executions that have occurred in the past 24 hours. 例如,本节说明了失败的执行次数与执行总次数,执行的持续时间(秒)和过去三个月内执行的平均持续时间。For example, this section shows the number of failed executions versus the total number of executions, the duration of an executions (in seconds), and the average duration of executions for over the past three months.

通过单击 “概述”“所有消息”“执行性能” ,可以查看有关包的其他信息。You can view additional information for a package by clicking Overview, All Messages, and Execution Performance.

“执行性能” 报告显示上次执行实例的持续时间、开始和结束时间以及应用的环境。The Execution Performance report shows the duration of the last execution instance, as well as the start and end times, and the environment that was applied.

“执行性能” 报告中包含的图表和关联的表显示过去 10 次成功执行包的持续时间。The chart and associated table included in the Execution Performance report shows the duration of the past 10 successful executions of the package. 该表还显示过去三个月内的平均执行持续时间。The table also shows the average execution duration over a three-month period. 在运行时可能会为这 10 次成功执行包应用不同的环境和不同的文字值。Different environments and different literal values may have been applied at runtime for these 10 successful executions of the package.

最后, “执行性能” 报告显示包数据流组件的活动时间和总时间。Finally, the Execution Performance report shows the Active Time and Total Time for the package data flow components. 活动时间指组件在所有阶段中用于执行的总共用时,而总时间指示组件占用的总时间。The Active Time refers to the total amount of time that component has spent executing in all phases, and the Total Time refers to the total time elapsed for a component. 如果上次包执行的日志记录级别设置为“性能”或“详细”,则该报告仅显示包组件的此信息。The report only displays this information for package components when the logging level of the last package execution was set to Performance or Verbose.

“概述” 报告显示包任务的状态。The Overview report shows the state of package tasks. “消息” 报告显示包和任务的事件消息和错误消息,如报告开始和结束时间,以及写入的行数。The Messages report shows the event messages and error messages for the package and tasks, such as reporting the start and end times, and the number of rows written.

还可以单击 “概述” 报告中的 “查看消息” ,导航到 “消息” 报告。You can also click View Messages in the Overview report to navigate to the Messages report. 还可以单击 “消息” 报告中的 “查看概述” ,导航到 “概述” 报告。You can also click View Overview in the Messages report to navigate to the Overview report.

您可以通过单击 “筛选器” ,然后在 “筛选设置” 对话框中选择条件,对在任何页上显示的表进行筛选。You can filter the table displayed on any page by clicking Filter and then selecting criteria in the Filter Settings dialog. 可用的筛选条件依赖于要显示的数据。The filter criteria that are available depend on the data being displayed. 您可以通过在 “筛选设置” 对话框中单击排序图标,更改报告的排序顺序。You can change the sort order of the report by clicking the sort icon in the Filter Settings dialog.

“所有执行”报告All Executions Report

“所有执行” 报告显示已在服务器上执行的所有 Integration ServicesIntegration Services 执行的摘要。The All Executions Report displays a summary of all Integration ServicesIntegration Services executions that have been performed on the server. 可以存在示例包的多个执行。There can be multiple executions of the sample package. “Integration Services 面板” 报告不同,您可以配置 “所有执行” 报告以显示在日期范围内开始的执行。Unlike the Integration Services Dashboard report, you can configure the All Executions report to show executions that have started during a range of dates. 该日期可以跨多天、多月或多年。The dates can span multiple days, months, or years.

该报告显示以下信息部分。The report displays the following sections of information.

部分Section 描述Description
“筛选器”Filter 显示应用于该报告的当前筛选器,如开始时间范围。Shows the current filter applied to the report, such as the Start time range.
执行信息Execution Information 显示每个包执行的开始时间、结束时间和持续时间。您可以查看用于包执行的参数值列表,如使用“执行包”任务传递给子包的值。Shows the start time, end time, and duration for each package execution.You can view a list of the parameter values that were used with a package execution, such as values that were passed to a child package using the Execute Package task. 若要查看参数列表,请单击“概述”。To view the parameter list, click Overview.

有关使用执行包任务使值可用于子包的详细信息,请参阅 Execute Package TaskFor more information about using the Execute Package task to make values available to a child package, see Execute Package Task.

有关参数的详细信息,请参阅 Integration Services (SSIS) 包和项目参数For more information about parameters, see Integration Services (SSIS) Package and Project Parameters.

所有连接All Connections

“所有连接” 报告提供以下有关已失败的连接的信息,有关 SQL ServerSQL Server 实例上已发生的执行的信息。The All Connections report provides the following information for connections that have failed, for executions that have occurred on the SQL ServerSQL Server instance.

该报告显示以下信息部分。The report displays the following sections of information.

部分Section 描述Description
“筛选器”Filter 显示应用于此报告的当前筛选器,如具有指定字符串的连接和 “上次失败时间” 范围。Shows the current filter applied to the report, such as connections with a specified string and the Last failed time range.

可以设置 “上次失败时间” 范围以仅显示在日期范围内发生的连接失败。You set the Last failed time range to display only connection failures that occurred during a range of dates. 该范围可以跨多个天、月或年。The range can span multiple days, months, or years.
详细信息Details 显示连接字符串、某个连接失败过程中的执行次数和上次连接失败的日期。Shows the connection string, number of executions during which a connection failed, and the date when the connection last failed.

“所有操作”报告All Operations Report

“所有操作” 报告显示已在服务器上执行的所有 Integration ServicesIntegration Services 操作的摘要,包括包部署、验证和执行以及其他管理操作。The All Operations Report displays a summary of all Integration ServicesIntegration Services operations that have been performed on the server, including package deployment, validation, and execution, as well as other administrative operations. 与 Integration Services 面板一样,您可以将筛选器应用于表,以便缩小显示的信息的范围。As with the Integration Services Dashboard, you can apply a filter to the table to narrow down the information displayed.

“所有验证”报告All Validations Report

“所有验证” 报告显示已在服务器上执行的所有 Integration ServicesIntegration Services 验证的摘要。The All Validations Report displays a summary of all Integration ServicesIntegration Services validations that have been performed on the server. 摘要中显示每次验证的信息,如状态、开始时间和结束时间。The summary displays information for each validation such as status, start time, and end time. 每个摘要条目包含一个指向在验证期间生成的消息的链接。Each summary entry includes a link to messages generated during validation. 与 Integration Services 面板一样,您可以将筛选器应用于表,以便缩小显示的信息的范围。As with the Integration Services Dashboard, you can apply a filter to the table to narrow down the information displayed.

自定义报告Custom Reports

可以将自定义报告(.rdl 文件)添加到 SQL Server Management StudioSQL Server Management Studio 中“Integration Services 目录”节点下的 SSISDB 目录节点 。You can add a custom report (.rdl file) to the SSISDB catalog node under the Integration Services Catalogs node in SQL Server Management StudioSQL Server Management Studio. 在添加报告前,请确认正在使用第三方命名约定以完全限定您引用的对象(如源表)。Before adding the report, confirm that you are using a three-part naming convention to fully qualify the objects you reference such as a source table. 否则, SQL Server Management StudioSQL Server Management Studio 将显示错误。Otherwise, SQL Server Management StudioSQL Server Management Studio will display an error. 命名约定为 <数据库>.<所有者>.<对象>。The naming convention is <database>.<owner>.<object>. 例如,SSISDB.internal.executions。An example would be SSISDB.internal.executions.

备注

将自定义报告添加到“数据库” 节点下的 SSISDB 节点时,可以不使用 SSISDB 前缀。When you add custom reports to the SSISDB node under the Databases node, the SSISDB prefix is not necessary.

有关如何创建和添加自定义报告的说明,请参阅 Add a Custom Report to Management StudioFor instructions on how to create and add a custom report, see Add a Custom Report to Management Studio.

查看 Integration Services 服务器的报告View Reports for the Integration Services Server

在当前版本的 SQL ServerSQL ServerIntegration ServicesIntegration Services中, SQL Server Management StudioSQL Server Management Studio 提供了标准报告,帮助你监视部署到 Integration ServicesIntegration Services 服务器的 Integration ServicesIntegration Services 项目。In the current release of SQL ServerSQL ServerIntegration ServicesIntegration Services, standard reports are available in SQL Server Management StudioSQL Server Management Studio to help you monitor Integration ServicesIntegration Services projects that have been deployed to the Integration ServicesIntegration Services server. 有关报表的详细信息,请参阅 Integration Services 服务器的报表For more information about the reports, see Reports for the Integration Services Server.

查看 Integration Services 服务器的报告To view reports for the Integration Services server

  1. SQL Server Management StudioSQL Server Management Studio中,展开对象资源管理器中的 “Integration Services 目录” 节点。In SQL Server Management StudioSQL Server Management Studio, expand the Integration Services Catalogs node in Object Explorer.

  2. 右键单击“SSISDB” ,单击“报表” ,然后单击“标准报表” 。Right-click SSISDB, click Reports, and then click Standard Reports.

  3. 单击以下一个或多个选项以查看报告。Click one more of the following to view a report.

    • Integration Services 面板Integration Services Dashboard

    • 所有执行All Executions

    • 所有验证All Validations

    • 所有操作All Operations

    • 所有连接All Connections

另请参阅See Also

项目和包的执行 Execution of Projects and Packages
对包执行进行故障排除的报告Troubleshooting Reports for Package Execution