对包执行进行故障排除的工具Troubleshooting Tools for Package Execution

适用于:是 SQL Server 是 Azure 数据工厂中的 SSIS Integration Runtime 是Azure Synapse Analytics (SQL DW) APPLIES TO: yesSQL Server yesSSIS Integration Runtime in Azure Data Factory yesAzure Synapse Analytics (SQL DW)

Integration ServicesIntegration Services 包括可用于在包完成和部署以后的执行过程中进行故障排除的功能和工具。includes features and tools that you can use to troubleshoot packages when you execute them after they have been completed and deployed.

在设计时, SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) 提供了用于暂停包的执行的断点、“进度”窗口和用于在数据通过数据流时查看数据的数据查看器。At design time, SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) provides breakpoints to pause package execution, the Progress window, and data viewers to watch your data as it passes through the data flow. 但是,这些功能在包部署后运行包时不再可用。However, these features are not available when you are running packages that have been deployed. 用于排除已部署包故障的主要技术如下:The main techniques for troubleshooting deployed packages are as follows:

  • 使用事件处理程序捕获和处理包错误。Catch and handle package errors by using event handlers.

  • 使用错误输出捕获错误数据。Capture bad data by using error outputs.

  • 使用日志记录跟踪包的执行步骤。Track the steps of package execution by using logging.

还可以使用下列技巧和技术避免正在运行的包的问题You can also use the following tips and techniques to avoid problems with running packages

使用事件处理程序捕获和处理包错误Catch and Handle Package Errors by Using Event Handlers

通过使用事件处理程序,您可以响应许多由包和包中的对象引发的事件。You can respond to the many events that are raised by the package and the objects in the package by using event handlers.

  • 创建 OnError 事件的事件处理程序Create an event handler for the OnError event. 在事件处理程序中,您可以使用发送邮件任务将故障通知给管理员,使用脚本任务和自定义逻辑获取用于故障排除的系统信息或者清除临时资源或不完整的输出。In the event handler, you can use a Send Mail task to notify an administrator of the failure, use a Script task and custom logic to obtain system information for troubleshooting, or clean up temporary resources or incomplete output. 有关详细信息,请参阅 Integration Services (SSIS) 事件处理程序For more information, see Integration Services (SSIS) Event Handlers.

使用错误输出对错误数据进行故障排除Troubleshoot Bad Data by Using Error Outputs

您可以使用对许多数据流组件可用的错误输出将包含错误的行定向到单独的目标中,供以后分析。You can use the error output available on many data flow components to direct rows that contain errors to a separate destination for later analysis. 有关详细信息,请参阅 数据中的错误处理For more information, see Error Handling in Data.

  • 使用错误输出捕获错误数据Capture bad data by using error outputs. 将包含错误的行发送到单独的目标(如错误表或文本文件)。Send rows that contain errors to a separate destination such as an error table or a text file. 错误输出自动添加两个数值列(包含导致该行被拒绝的错误的编号)以及发生该错误的列的 ID。The error output automatically adds two numeric columns that contain the number of the error that caused the row to be rejected, and the ID of the column in which the error occurred.

  • 向错误输出添加易于理解的信息Add friendly information to the error outputs. 除了由错误输出提供的两个数字标识符外,你还可以添加错误消息和列名称以便更容易分析错误输出。You can make it easier to analyze the error output by adding the error message and the column name in addition to the two numeric identifiers that are supplied by the error output. 有关如何通过使用脚本添加这两个附加列的示例,请参阅 使用脚本组件增强错误输出For an example of how to add these two additional columns by using scripting, see Enhancing an Error Output with the Script Component.

  • 或者,通过记录 DiagnosticEx 事件获取列名称Or, get the column names by logging the DiagnosticEx event. 此事件将数据流沿袭映射写入到日志中。This event writes a data flow lineage map to the log. 然后就可以使用由错误输出捕获的列标识符来查找此沿袭映射中的列名称。You can then look up the column name in this lineage map by using the column identifier captured by an error output. 有关详细信息,请参阅数据中的错误处理For more info, see Error Handling in Data.

    DiagnosticEx 的消息列的值是 XML 文本。The value of the message column for DiagnosticEx is XML text. 若要查看包执行的消息文本,请查询 catalog.operation_messages(SSISDB 数据库)视图。To view the message text for a package execution, query the catalog.operation_messages (SSISDB Database) view. 请注意,为了缩减日志大小, DiagnosticEx 事件不在其 XML 输出中保留空白。Note that the DiagnosticEx event does not preserve whitespace in its XML output to reduce the size of the log. 若要提高可读性,请将日志复制到支持 XML 格式和语法突出显示的 XML 编辑器中 - 例如 Visual Studio 中的 XML 编辑器。To improve readability, copy the log into an XML editor - in Visual Studio, for example - that supports XML formatting and syntax highlighting.

使用操作报告对包执行进行故障排除Troubleshoot Package Execution by Using Operations Reports

SQL Server Management StudioSQL Server Management Studio 中提供标准操作报告,帮助您监视部署到 Integration ServicesIntegration Services 目录的 Integration ServicesIntegration Services 包。Standard operations reports are available in SQL Server Management StudioSQL Server Management Studio to help you monitor Integration ServicesIntegration Services packages that have been deployed to the Integration ServicesIntegration Services catalog. 这些包报告有助于您查看包状态和历史记录,并根据需要确定执行失败的原因。These package reports help you to view package status and history, and, if necessary, identify the cause of failures.

有关详细信息,请参阅 Troubleshooting Reports for Package ExecutionFor more information, see Troubleshooting Reports for Package Execution.

使用 SSISDB 视图对包执行进行故障排除Troubleshoot Package Execution by Using SSISDB Views

提供了一些 SSISDB 数据库视图,您可以查询它们来监视包执行和其他操作信息。A number of SSISDB database views are available that you can query to monitor package execution and other operations information. 有关详细信息,请参阅 监视包运行和其他操作For more information, see Monitor Running Packages and Other Operations.

使用日志记录对包执行进行故障排除Troubleshoot Package Execution by Using Logging

通过启用日志记录,您可以跟踪正在运行的包中所发生的大部分问题。You can track much of what occurs in your running packages by enabling logging. 日志提供程序捕获有关指定事件的信息供以后分析,并且以数据库表、平面文件、XML 文件或支持的其他输出格式保存该信息。Log providers capture information about the specified events for later analysis, and save that information in a database table, a flat file, an XML file, or another supported output format.

  • 启用日志记录Enable logging. 您可以只选择所要捕获的事件以及只选择所要捕获的信息项,以修改日志记录输出。You can refine the logging output by selecting only the events and only the items of information that you want to capture. 有关详细信息,请参阅 Integration Services (SSIS) 日志记录For more information, see Integration Services (SSIS) Logging.

  • 选择包的“诊断”事件对访问接口问题进行故障排除。Select the package's Diagnostic event to troubleshoot provider issues. 一些日志记录消息可以帮助您对包与外部数据源的交互情况进行故障排除。There are logging messages that help you troubleshoot a package's interaction with external data sources. 有关详细信息,请参阅 Troubleshooting Tools Package ConnectivityFor more information, see Troubleshooting Tools Package Connectivity.

  • 增强默认日志记录输出Enhance the default logging output. 通常,每次该包运行时日志记录都会向日志记录目标中追加行。Logging typically appends rows to the logging destination each time that a package is run. 虽然日志记录输出的每一行都使用包的名称和唯一标识符来标识包,也通过唯一的 ExecutionID 来标识包的执行,但单一列表中的大量日志记录输出会使分析变得困难。Although each row of logging output identifies the package by its name and unique identifier, and also identifies the execution of the package by a unique ExecutionID, the large quantity of logging output in a single list can become difficult to analyze.

    建议采用下列方法增强默认日志记录输出并使其更容易生成报告:The following approach is one suggestion for enhancing the default logging output and making it easier to generate reports:

    1. 创建记录包的每次执行情况的父表Create a parent table that logs each execution of a package. 此父表只有一行用于包的每次执行,并使用 ExecutionID 链接到 Integration ServicesIntegration Services 日志记录表中的子记录。This parent table has only a single row for each execution of a package, and uses the ExecutionID to link to the child records in the Integration ServicesIntegration Services logging table. 您可以在每个包的开始使用一个执行 SQL 任务创建此新行并记录开始时间。You can use an Execute SQL task at the beginning of each package to create this new row and to record the start time. 然后您可以在包的结尾使用另一个执行 SQL 任务,用结束时间、持续时间和状态来更新该行。Then you can use another Execute SQL task at the end of the package to update the row with the end time, duration, and status.

    2. 将审核信息添加到数据流Add auditing information to the data flow. 您可以使用审核转换将有关创建或修改每一行的包执行的信息添加到数据流的行中。You can use the Audit transformation to add information to rows in the data flow about the package execution that created or modified each row. 审核转换可以提供九条信息,包括 PackageName 和 ExecutionInstanceGUID。The Audit transformation makes nine pieces of information available, including the PackageName and ExecutionInstanceGUID. 有关详细信息,请参阅 Audit TransformationFor more information, see Audit Transformation. 如果您希望在每一行中包括自定义信息以便审核,可以使用派生列转换将此信息添加到数据流的行中。If you have custom information that you would also like to include in each row for auditing purposes, you can add this information to rows in the data flow by using a Derived Column transformation. 有关详细信息,请参阅 派生列转换For more information, see Derived Column Transformation.

    3. 考虑捕获行计数数据Consider capturing row count data. 考虑另行创建一个表,用于存储行计数信息。其中,每个包执行实例均由其 ExecutionID 标识。Consider creating a separate table for row count information, where each instance of package execution is identified by its ExecutionID. 使用行计数转换在数据流的关键点将行计数保存到一系列变量中。Use the Row Count transformation to save the row count into a series of variables at critical points in the data flow. 数据流结束后,请使用执行 SQL 任务将该系列值插入到表的行中,供以后分析和报告。After the data flow ends, use an Execute SQL task to insert the series of values into a row in the table for later analysis and reporting.

    有关此方法的详细信息,请参阅 MicrosoftMicrosoft 白皮书 Project REAL:Business Intelligence ETL 设计实践中的“ETL 审核和日志记录”一节。For more information about this approach, see the section, "ETL Auditing and Logging," in the MicrosoftMicrosoft white paper, Project REAL: Business Intelligence ETL Design Practices.

使用调试转储文件对包执行进行故障排除Troubleshoot Package Execution by Using Debug Dump Files

Integration ServicesIntegration Services中,可以创建提供包执行信息的调试转储文件。In Integration ServicesIntegration Services, you can create debug dump files that provide information about the execution of a package. 有关详细信息,请参阅 生成包执行的转储文件For more information, see Generating Dump Files for Package Execution.

针对运行时验证问题进行故障排除Troubleshoot Run-time Validation Issues

有时在包中前一个任务执行完成之前,您也许不能连接到数据源,或者无法验证包的某些部分。Sometimes you might not be able to connect to your data sources, or portions of your package cannot be validated, until prior tasks in the package have executed. Integration ServicesIntegration Services 包括以下功能,可帮助您避免由这些情况导致的验证错误:includes the following features to help you avoid the validation errors that would otherwise result from these conditions:

  • 配置加载包时无效包元素的 DelayValidation 属性Configure the DelayValidation property on package elements that are not valid when the package is loaded. 您可以将具有无效配置的包元素的 DelayValidation 设置为 True ,以防止包加载时的验证错误。You can set DelayValidation to True on package elements whose configuration is not valid, to prevent validation errors when the package is loaded. 例如,可以使用只有在运行时通过执行 SQL 任务创建才存在的目标表来执行数据流任务。For example, you may have a Data Flow task that uses a destination table that does not exist until an Execute SQL task creates the table at run time. 可以在包级或包中的各个任务和容器级启用 DelayValidation 属性。The DelayValidation property can be enabled at the package level, or at the level of the individual tasks and containers that the package includes.

    可以对数据流任务设置 DelayValidation 属性,但不能对单个数据流组件设置该属性。The DelayValidation property can be set on a Data Flow task, but not on individual data flow components. 可以通过将单个数据流组件的 ValidateExternalMetadata 属性设置为 falseYou can achieve a similar effect by setting the ValidateExternalMetadata property of individual data flow components to false. 但是,当此属性的值为 false时,该组件不能识别外部数据源的元数据的更改。However, when the value of this property is false, the component is not aware of changes to the metadata of external data sources. 设置为 true时, ValidateExternalMetadata 属性可帮助避免数据库中由于锁定导致的阻塞问题,特别是在包使用事务时。When set to true, the ValidateExternalMetadata property can help to avoid blocking issues caused by locking in the database, especially when the package is using transactions.

运行时权限问题的故障排除Troubleshoot Run-time Permissions Issues

如果您在尝试使用 SQL ServerSQL Server 代理运行已部署的包时遇到错误,则代理所使用的帐户可能不具备必需的权限。If you encounter errors when trying to run deployed packages by using SQL ServerSQL Server Agent, the accounts used by Agent might not have the required permissions. 有关如何对从 SQL ServerSQL Server 代理作业运行的包进行故障排除的信息,请参阅 从 SQL Server 代理作业步骤调用 SSIS 包时 SSIS 包不运行For information on how to troubleshoot packages that are run from SQL ServerSQL Server Agent jobs, see An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step. 有关如何从 SQL ServerSQL Server 代理作业运行包的详细信息,请参阅 包的 SQL Server 代理作业For more information on how to run packages from SQL ServerSQL Server Agent jobs, see SQL Server Agent Jobs for Packages.

若要连接到 Excel 或 Access 数据源, SQL ServerSQL Server 代理要求帐户具有在 TEMP 和 TMP 环境变量所指定的在文件夹中读取、写入、创建和删除临时文件的权限。To connect to Excel or Access data sources, SQL ServerSQL Server Agent requires an account that has permission to read, write, create, and delete temporary files in the folder that is specified by the TEMP and TMP environment variables.

64 位问题的故障排除Troubleshoot 64-bit Issues

  • 部分数据访问接口在 64 位平台上不可用Some data providers are not available on the 64-bit platform. 尤其是,需要连接到 Excel 或 Access 数据源的 MicrosoftMicrosoft Jet OLE DB 访问接口在 64 位版本中不可用。In particular, the MicrosoftMicrosoft Jet OLE DB Provider that is required to connect to Excel or Access data sources is not available in a 64-bit version.

无说明错误的故障排除Troubleshoot Errors without a Description

如果遇到了没有附带说明的 Integration ServicesIntegration Services 错误,您可以根据其错误号在 Integration Services 错误和消息引用 中查找错误来找到说明。If you encounter an Integration ServicesIntegration Services error that does not have an accompanying description, you can locate the description in Integration Services Error and Message Reference by looking up the error by its number. 目前该列表中不包括故障排除信息。The list does not include troubleshooting information at this time.

调试数据流Debugging Data Flow

dougbert.com 上的博客文章: 向错误输出中添加错误列名Blog entry, Adding the error column name to an error output, on dougbert.com.