包开发的故障排除工具Troubleshooting Tools for Package Development

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是 Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factoryyes Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factory适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是 Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factoryyes Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factory

Integration ServicesIntegration Services 包括在 SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中开发包的过程中可用于对包进行故障排除的功能和工具。includes features and tools that you can use to troubleshoot packages while you are developing them in SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT).

设计时验证问题故障排除Troubleshooting Design-time Validation Issues

Integration ServicesIntegration Services的当前版本中,当打开包后,系统将在验证所有数据流组件值之前验证所有连接,并设置速度较慢或无法脱机工作的所有连接。In the current release of Integration ServicesIntegration Services, when a package is opened, the system validates all connections before validating all of the data flow components and sets any connections that are slow or unavailable to work offline. 这有助于减少验证包数据流时的延迟时间。This helps reduce the delay in validating the package data flow.

打开包后,还可以通过右键单击“连接管理器”区域中的连接管理器并单击“脱机工作”来关闭连接。After a package is opened, you can also turn off a connection by right-clicking the connection manager in the Connection Managers area and then clicking Work Offline. 这可以在 SSIS 设计器中加快执行操作。This can speed up operations in the SSIS Designer.

已设置为脱机工作的连接将保持脱机状态,直到您执行下列操作之一:Connections that have been set to work offline, will remain offline until you do one of the following:

  • 通过右键单击 SSIS 设计器的“连接管理器”区域中的连接管理器并单击“测试连接”来测试连接。Test the connection by right-clicking the connection manager in the Connection Managers area of SSIS Designer and then clicking Test Connectivity.

    例如,当打开包后,连接最初设置为脱机工作。For example, a connection is initially set to work offline when the package is opened. 修改连接字符串以解决该问题,并单击 “测试连接” 以测试连接。You modify the connection string to resolve the issue and click Test Connectivity to test the connection.

  • 重新打开包或重新打开包含该包的项目。Re-open the package or re-open the project that contains the package. 将重新对包中的所有连接进行验证。Validation is run again on all of the connections in the package.

Integration ServicesIntegration Services 包括以下附加功能,可帮助您避免验证错误:includes the following, additional features to help you avoid validation errors :

  • 在数据源不可用时,将所有包和所有连接设置为脱机工作Set all of the package and all of the connections to work offline when data sources are not available. 您可以从 “SSIS” 菜单启用 “脱机工作”You can enable Work Offline from the SSIS menu. DelayValidation 属性不同, “脱机工作” 选项即使在包打开之前也可用。Unlike the DelayValidation property, the Work Offline option is available even before you open a package. 您还可以启用 “脱机工作” 以加快设计器中操作的速度,而仅在需要验证包的时候再禁用此选项。You can also enable Work Offline to speed up operations in the designer, and disable it only when you want your package to be validated.

  • 针对在运行时之前无效的包元素配置 DelayValidation 属性Configure the DelayValidation property on package elements that are not valid until run time. 对于其配置在设计时无效的包元素,您可以将 DelayValidation 设置为 True 以防止出现验证错误。You can set DelayValidation to True on package elements whose configuration is not valid at design time to prevent validation errors. 例如,可以使用只有在运行时通过执行 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. 在部署包时,对于相同的包元素,通常必须将此属性设置为 True ,以防止运行时出现相同的验证错误。Normally you must leave this property set to True on the same package elements when you deploy the package, to prevent the same validation errors at run time.

    可以对数据流任务设置 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.

如果进行验证时包使用的数据库对象被锁定,则验证过程可能会停止响应。If database objects that are used by the package are locked when validation occurs, the validation process might stop responding. 在这些情况下, SSISSSIS 设计器也会停止响应。In these circumstances, the SSISSSIS Designer also stops responding. 可以使用 Management StudioManagement Studio 继续验证过程,以结束 SQL ServerSQL Server中的关联会话。You can resume validation by using Management StudioManagement Studio to close the associated session in SQL ServerSQL Server. 还可以使用本节中介绍的设置避免此问题。You can also avoid this issue by using the settings described in this section.

控制流故障排除Troubleshooting Control Flow

Integration ServicesIntegration Services 包括以下功能和工具,可用于在包开发过程中对包中的控制流进行故障排除:includes the following features and tools that you can use to troubleshoot the control flow in packages during package development:

  • 对任务、容器和包设置断点Set breakpoints on tasks, containers, and the package. 可以使用 SSISSSIS 设计器提供的图形工具设置断点。You can set breakpoints by using the graphical tools that SSISSSIS Designer provides. 可以在包级或包中的各个任务和容器级启用断点。Breakpoints can be enabled at the package level, or at the level of the individual tasks and containers that the package includes. 某些任务和容器提供用于设置断点的其他中断条件。Some tasks and containers provide additional break conditions for setting breakpoints. 例如,可以对 For 循环容器启用一个中断条件,该中断条件在循环的每次迭代开始时挂起执行。For example, you can enable a break condition on the For Loop container that suspends execution at the start of each iteration of the loop.

  • 使用调试窗口Use the debugging windows. 在运行具有断点的包时, SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) 中的调试窗口提供对变量值和状态消息的访问。When you run a package that has breakpoints, the debug windows in SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) provide access to variable values and status messages.

  • 查看“进度”选项卡中的信息SSISSSIS 设计器提供有关在 SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中运行包时控制流的其他信息。Review the information on the Progress tab. SSISSSIS Designer provides additional information about control flow when you run a package in SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT). “进度”选项卡按执行顺序列出任务和容器,而且还包括每个任务和容器及包自身的开始时间和结束时间、警告以及错误消息。The Progress tab lists tasks and containers in order of execution and includes start and finish times, warnings, and error messages for each task and container, including the package itself.

有关这些功能的详细信息,请参阅 Debugging Control FlowFor more information on these features, see Debugging Control Flow.

数据流故障排除Troubleshooting Data Flow

Integration ServicesIntegration Services 包括以下功能和工具,可用于在包开发过程中对包中的数据流进行故障排除:includes the following features and tools that you can use to troubleshoot the data flows in packages during package development:

  • 仅使用数据的子集进行测试Test with only a subset of your data. 如果只想通过使用一个数据集的示例对包中的数据流进行故障排除,您可以包括百分比抽样转换或行抽样转换,以便在运行时创建一个内联的数据示例。If you want to troubleshoot the data flow in a package by using only a sample of the dataset, you can include a Percentage Sampling or Row Sampling transformation to create an in-line data sample at run time. 有关详细信息,请参阅 Percentage Sampling TransformationRow Sampling TransformationFor more information, see Percentage Sampling Transformation and Row Sampling Transformation.

  • 使用数据查看器监视在数据流中移动的数据Use data viewers to monitor data as it moves through the data flow. 数据查看器随着数据在源、转换和目标之间的移动显示相关数据值。Data viewers display data values as the data moves between sources, transformations, and destinations. 数据查看器可以在网格中显示数据。A data viewer can display data in a grid. 您可以将数据从数据查看器复制到剪贴板中,然后再将数据粘贴到文件或 Excel 电子表格中。You can copy the data from a data viewer to the Clipboard, and then paste the data into a file or Excel spreadsheet. 有关详细信息,请参阅 Debugging Data Flow 中开发包的过程中可用于对包进行故障排除的功能和工具。For more information, see Debugging Data Flow .

  • 对支持错误输出的数据流组件配置错误输出Configure error outputs on data flow components that support them. 许多数据流的源、转换和目标也支持错误输出。Many data flow sources, transformations, and destinations also support error outputs. 通过配置数据流组件的错误输出,可以将包含错误的数据定向到不同的目标。By configuring the error output of a data flow component, you can direct data that contains errors to a different destination. 例如,您可以将失败或截断的数据捕获到一个单独的文本文件中。For example, you can capture the data that failed or was truncated in a separate text file. 也可以将数据查看器附加到错误输出,而且只检查错误数据。You can also attach data viewers to error outputs and examine only the erroneous data. 在设计时,错误输出捕获错误的数据值,以帮助您开发可高效处理实际数据的包。At design time, error outputs capture troublesome data values to help you develop packages that deal effectively with real-world data. 不过,其他故障排除工具和功能仅在设计时有用,而错误输出在生产环境中仍然非常有用。However, while other troubleshooting tools and features are useful only at design time, error outputs retain their usefulness in the production environment. 有关详细信息,请参阅 数据中的错误处理For more information, see Error Handling in Data.

  • 捕获处理的行数Capture the count of rows processed. SSISSSIS 设计器中运行包时,通过某路径传递的行数将显示在数据流设计器中。When you run a package in SSISSSIS Designer, the number of rows that have passed through a path is displayed in the data flow designer. 随着数据在路径中的移动,该数量会定期更新。This number is updated periodically while the data moves through the path. 您还可以将行计数转换添加到数据流中,以捕获变量中的最终行计数。You can also add a Row Count transformation to the data flow to capture the final row count in a variable. 有关详细信息,请参阅 Row Count TransformationFor more information, see Row Count Transformation.

  • 查看“进度”选项卡中的信息SSISSSIS 设计器提供有关在 SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中运行包时数据流的其他信息。Review the information on the Progress tab. SSISSSIS Designer provides additional information about data flows when you run a package in SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT). “进度”选项卡按执行顺序列出数据流组件,而且还包括包的每个阶段的进度(显示为完成百分比)以及写入目标的行数。The Progress tab lists data flow components in order of execution and includes information about progress for each phase of the package, displayed as percentage complete, and the number of rows written to the destination.

有关这些功能的详细信息,请参阅 Debugging Data FlowFor more information on these features, see Debugging Data Flow.

脚本故障排除Troubleshooting Scripts

MicrosoftMicrosoft Visual StudioVisual Studio Tools for Applications (VSTA) 是一种可以用来编写脚本任务和脚本组件所使用脚本的开发环境。Visual StudioVisual Studio Tools for Applications (VSTA) is the development environment in which you write the scripts that are used by the Script task and Script component. VSTA 提供了可用于排除包开发过程中的脚本故障的以下功能和工具:VSTA provides the following features and tools that you can use to troubleshoot scripts during package development:

  • 在脚本任务的脚本中设置断点。Set breakpoints in script in Script tasks. VSTA 仅对脚本任务中的脚本提供调试支持。VSTA provides debugging support for scripts in the Script task only. 通过综合使用在脚本任务中设置的断点与对包以及包中的任务和容器设置的断点,可以对所有包元素进行无缝调试。The breakpoints that you set in Script tasks are integrated with the breakpoints that you set on packages and the tasks and containers in the package, enabling seamless debugging of all package elements.

    备注

    当调试一个包含多个脚本任务的包时,调试器将只命中其中一个脚本任务中的断点,而将忽略其他脚本任务中的断点。When you debug a package that contains multiple Script tasks, the debugger hits breakpoints in only one Script task and will ignore breakpoints in the other Script tasks. 如果脚本任务是 Foreach 循环容器或 For 循环容器的一部分,则调试器将在循环的第一次迭代之后忽略脚本任务中的断点。If a Script task is part of a Foreach Loop or For Loop container, the debugger ignores breakpoints in the Script task after the first iteration of the loop.

有关详细信息,请参阅 Debugging ScriptFor more information, see Debugging Script. 有关如何调试脚本组件的建议,请参阅 脚本组件的编码和调试For suggestions about how to debug the Script component, see Coding and Debugging the Script Component.

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

如果在包开发过程中遇到了没有附带说明的 Integration ServicesIntegration Services 错误号,可以在 Integration Services 错误和消息引用中查找说明。If you encounter an Integration ServicesIntegration Services error number without an accompanying description during package development, you can locate the description in Integration Services Error and Message Reference. 目前该列表中不包括故障排除信息。The list does not include troubleshooting information at this time.

另请参阅See Also

对包执行进行故障排除的工具 Troubleshooting Tools for Package Execution
数据流性能特点Data Flow Performance Features