Integration Services (SSIS) 日志记录Integration Services (SSIS) Logging

适用对象:是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

SQL ServerSQL Server Integration ServicesIntegration Services 包含可用来在包、容器和任务中执行日志记录的日志提供程序。Integration ServicesIntegration Services includes log providers that you can use to implement logging in packages, containers, and tasks. 通过日志记录可以捕获有关包的运行时信息,从而帮助您在每次运行包时对其进行审核和故障排除。With logging, you can capture run-time information about a package, helping you audit and troubleshoot a package every time it is run. 例如,日志可以捕获运行包的操作员的姓名以及包开始和完成的时间。For example, a log can capture the name of the operator who ran the package and the time the package began and finished.

您可以配置在 Integration ServicesIntegration Services 服务器上执行包的过程中出现的日志记录范围。You can configure the scope of logging that occurs during a package execution on the Integration ServicesIntegration Services server. 有关详细信息,请参阅 在 SSIS 服务器上启用包执行的日志记录For more information, see Enable Logging for Package Execution on the SSIS Server.

在使用 dtexec 命令提示实用工具运行包时,还可以包括日志记录。You can also include logging when you run a package using the dtexec command prompt utility. 有关支持日志记录的命令提示参数的详细信息,请参阅 dtexec UtilityFor more information about the command prompt arguments that support logging, see dtexec Utility.

在 SQL Server Data Tools 中配置日志记录Configure Logging in SQL Server Data Tools

日志与包关联,而且可以在包级进行配置。Logs are associated with packages and are configured at the package level. 包中的每项任务或容器都可以将信息记录到任何包日志中。Each task or container in a package can log information to any package log. 即使不对包本身启用日志记录,也可以对包中的任务和容器启用日志记录。The tasks and containers in a package can be enabled for logging even if the package itself is not. 例如,您可以对执行 SQL 任务启用日志记录,而不对父包启用日志记录。For example, you can enable logging on an Execute SQL task without enabling logging on the parent package. 包、容器或任务都可以将信息写入多个日志中。A package, container, or task can write to multiple logs. 可以只在包上启用日志记录,也可以选择在包所包括的任何单个任务或容器上启用日志记录。You can enable logging on the package only, or you can choose to enable logging on any individual task or container that the package includes.

将日志添加到包时,请选择日志提供程序和日志的位置。When you add the log to a package, you choose the log provider and the location of the log. 日志提供程序指定日志数据的格式:例如 SQL ServerSQL Server 数据库或文本文件。The log provider specifies the format for the log data: for example, a SQL ServerSQL Server database or text file.

Integration ServicesIntegration Services 包含下列日志提供程序:includes the following log providers:

  • 文本文件日志提供程序,将日志项以逗号分隔值 (CSV) 格式写到 ASCII 文本文件。The Text File log provider, which writes log entries to ASCII text files in a comma-separated value (CSV) format. 这种提供程序的默认文件扩展名是 .log。The default file name extension for this provider is .log.

  • SQL Server ProfilerSQL Server Profiler 日志提供程序,写入可用 SQL ServerSQL Server 事件探查器查看的跟踪。The SQL Server ProfilerSQL Server Profiler log provider, which writes traces that you can view using SQL ServerSQL Server Profiler. 这种提供程序的默认文件扩展名是 .trc。The default file name extension for this provider is .trc.

    备注

    无法在以 64 位模式运行的包中使用 SQL Server ProfilerSQL Server Profiler 日志提供程序。You cannot use the SQL Server ProfilerSQL Server Profiler log provider in a package that is running in 64-bit mode.

  • SQL ServerSQL Server 日志提供程序,将日志项写入 数据库中的 sysssislog SQL ServerSQL Server 表。The SQL ServerSQL Server log provider, which writes log entries to the sysssislog table in a SQL ServerSQL Server database.

  • Windows 事件日志提供程序,将日志项写入本地计算机上 Windows 事件日志中的应用程序日志。The Windows Event log provider, which writes entries to the Application log in the Windows Event log on the local computer.

  • XML 文件日志提供程序,将日志文件写入 XML 文件。The XML File log provider, which writes log files to an XML file. 这种提供程序的默认文件扩展名是 .xml。The default file name extension for this provider is .xml.

如果要将日志提供程序添加到包或者以编程方式配置日志记录,可以使用 ProgID 或 ClassID 标识日志提供程序,而不必使用 SSISSSIS 设计器在 “配置 SSIS 日志” 对话框中显示的名称。If you add a log provider to a package or configure logging programmatically, you can use either a ProgID or ClassID to identify the log provider, instead of using the names that SSISSSIS Designer displays in the Configure SSIS Logs dialog box.

下表列出了用于 Integration ServicesIntegration Services 所包含日志提供程序的 ProgID 和 ClassID,以及日志提供程序写入的日志的位置。The following table lists the ProgID and ClassID for the log providers that Integration ServicesIntegration Services includes, and the location of the logs to which log providers write.

日志提供程序Log provider ProgIDProgID ClassIDClassID 位置Location
文本文件Text file DTS.LogProviderTextFileDTS.LogProviderTextFile {0A039101-ACC1-4E06-943F-279948323883}{0A039101-ACC1-4E06-943F-279948323883} 日志提供程序使用的文件连接管理器指定此文本文件的路径。The File connection manager that the log provider uses specifies the path of the text file.
SQL Server ProfilerSQL Server Profiler DTS.LogProviderSQLProfilerDTS.LogProviderSQLProfiler {E93F6300-AE0C-4916-A7BF-A8D0CE12C77A}{E93F6300-AE0C-4916-A7BF-A8D0CE12C77A} 日志提供程序使用的文件连接管理器指定 SQL Server ProfilerSQL Server Profiler所使用的文件的路径。The File connection manager that the log provider uses specifies the path of the file used by SQL Server ProfilerSQL Server Profiler.
SQL ServerSQL Server DTS.LogProviderSQLServerDTS.LogProviderSQLServer {94150B25-6AEB-4C0D-996D-D37D1C4FDEDA}{94150B25-6AEB-4C0D-996D-D37D1C4FDEDA} 日志提供程序使用的 OLE DB 连接管理器指定包含存储日志项的 sysssislog 表的 SQL ServerSQL Server 数据库。The OLE DB connection manager that the log provider uses specifies the SQL ServerSQL Server database that contains the sysssislog table with the log entries.
Windows 事件日志Windows Event Log DTS.LogProviderEventLogDTS.LogProviderEventLog {071CC8EB-C343-4CFF-8D58-564B92FCA3CF}{071CC8EB-C343-4CFF-8D58-564B92FCA3CF} Windows 事件查看器中的应用程序日志包含 Integration ServicesIntegration Services 日志信息。The Application log in Windows Event Viewer contains the Integration ServicesIntegration Services log information.
XML 文件XML File DTS.LogProviderXMLFileDTS.LogProviderXMLFile {440945A4-2A22-4F19-B577-EAF5FDDC5F7A}{440945A4-2A22-4F19-B577-EAF5FDDC5F7A} 日志提供程序使用的文件连接管理器指定 XML 文件的路径。The File connection manager that the log provider uses specifies the path of the XML file.

也可以创建自定义日志提供程序。You can also create custom log providers. 有关详细信息,请参阅 Creating a Custom Log ProviderFor more information, see Creating a Custom Log Provider.

包中的日志提供程序是该包的日志提供程序集合的成员。The log providers in a package are members of the log providers collection of the package. 在使用 SSISSSIS 设计器创建包并实现日志记录时,您可以在 设计器的 “包资源管理器” 选项卡上的 “日志提供程序” SSISSSIS 文件夹中看到集合成员列表。When you create a package and implement logging by using SSISSSIS Designer, you can see a list of the collection members in the Log Provider folders on the Package Explorer tab of SSISSSIS Designer.

请提供日志提供程序的名称和说明,并指定日志提供程序使用的连接管理器,以配置日志提供程序。You configure a log provider by providing a name and description for the log provider and specifying the connection manager that the log provider uses. SQL ServerSQL Server 日志提供程序使用 OLE DB 连接管理器。The SQL ServerSQL Server log provider uses an OLE DB connection manager. 文本文件、 SQL Server ProfilerSQL Server Profiler和 XML 文件日志提供程序全都使用文件连接管理器。The Text File, SQL Server ProfilerSQL Server Profiler, and XML File log providers all use File connection managers. Windows 事件日志提供程序不使用连接管理器,因为它直接写入 Windows 事件日志。The Windows Event log provider does not use a connection manager, because it writes directly to the Windows Event log. 有关详细信息,请参阅 OLE DB Connection ManagerFile Connection ManagerFor more information, see OLE DB Connection Manager and File Connection Manager.

自定义日志记录Logging Customization

为了自定义事件或自定义消息的日志记录, Integration ServicesIntegration Services 提供了要包括在日志项中的常用记录信息的架构。To customize the logging of an event or custom message, Integration ServicesIntegration Services provides a schema of commonly logged information to include in log entries. Integration ServicesIntegration Services 日志架构定义您可以记录的信息。The Integration ServicesIntegration Services log schema defines the information that you can log. 您可以从日志架构中为每个日志项选择元素。You can select elements from the log schema for each log entry.

包及其容器和任务不必记录相同的信息,同一包或容器内的任务可以记录不同的信息。A package and its containers and tasks do not have to log the same information, and tasks within the same package or container can log different information. 例如,包可以在包启动时记录操作员信息,一个任务可以记录任务失败的原因,而另一个任务可以在出现错误时记录信息。For example, a package can log operator information when the package starts, one task can log the source of the task's failure, and another task can log information when errors occur. 如果包及其容器和任务使用多个日志,则相同信息会写入所有日志中。If a package and its containers and tasks use multiple logs, the same information is written to all the logs.

通过指定要记录的事件以及要为每个事件记录的信息,您可以选择满足自己需求的日志记录级别。You can select a level of logging that suits your needs by specifying the events to log and the information to log for each event. 您可能会发现一些事件提供的信息更为有用。You may find that some events provide more useful information than others. 例如,对于 PreExecute 事件,您希望仅记录计算机名和操作员姓名,而对于 Error 事件,您希望记录所有可用的信息。For example, you might want to log only the computer and operator names for the PreExecute event but all available information for the Error event.

若要防止日志文件占用大量的磁盘空间或者避免过多的日志记录(这可能会降低性能),可以通过选择记录特定的事件和信息项来限制日志记录。To prevent log files from using large amounts of disk space, or to avoid excessive logging, which could degrade performance, you can limit logging by selecting specific events and information items to log. 例如,您可以将日志配置为仅捕获每个错误的日期和计算机名。For example, you can configure a log to capture only the date and the computer name for each error.

SSISSSIS 设计器中,您可以使用 “配置 SSIS 日志” 对话框来定义日志记录选项。In SSISSSIS Designer, you define the logging options by using the Configure SSIS Logs dialog box.

日志架构Log Schema

下表介绍了日志架构中的元素:The following table describes the elements in the log schema.

元素Element 描述Description
ComputerComputer 发生日志事件的计算机的名称。The name of the computer on which the log event occurred.
操作员Operator 启动包的用户的标识。The identity of the user who launched the package.
SourceNameSourceName 发生日志事件的容器或任务的名称。The name of the container or task in which the log event occurred.
SourceIDSourceID 发生日志事件的包、 For 循环容器、Foreach 循环容器、序列容器或任务的唯一标识符。The unique identifier of the package; the For Loop, Foreach Loop, or Sequence container; or the task in which the log event occurred.
ExecutionIDExecutionID 包执行实例的 GUID。The GUID of the package execution instance.

注意:运行一个包可能会创建有不同 ExecutionID 元素值的日志项目。Note: Running a single package might create log entries with different values for the ExecutionID element. 例如,当在 SQL Server Data ToolsSQL Server Data Tools中运行包时,验证阶段可能会创建 ExecutionID 元素与 SQL Server Data ToolsSQL Server Data Tools对应的日志项。For example, when you run a package in SQL Server Data ToolsSQL Server Data Tools, the validation phase might create log entries with an ExecutionID element that corresponds to SQL Server Data ToolsSQL Server Data Tools. 但是,执行阶段可能会创建 ExecutionID 元素与 dtshost.exe 对应的日志项。However, the execution phase might create log entries with an ExecutionID element that corresponds to dtshost.exe. 再比如,当运行包含“执行包”任务的包时,这些任务中的每个任务都会运行子包。For another example, when you run a package that contains Execute Package tasks, each of these tasks runs a child package. 这些子包创建的日志项所具有的 ExecutionID 元素可能不同于父包创建的日志项。These child packages might create log entries that have a different ExecutionID element than the log entries that the parent package creates.
MessageTextMessageText 与日志项关联的消息。A message associated with the log entry.
DataBytesDataBytes 日志项特定的字节数组。A byte array specific to the log entry. 此字段的意义因日志项的不同而不同。The meaning of this field varies by log entry.

下表介绍日志架构中三个附加元素,这些元素在 “配置 SSIS 日志” 对话框的 “详细信息” 选项卡中不可用。The following table describes three additional elements in the log schema that are not available on the Details tab of the Configure SSIS Logs dialog box.

元素Element 描述Description
StartTimeStartTime 容器或任务开始运行的时间。The time at which the container or task starts to run.
EndTimeEndTime 容器或任务停止运行的时间。The time at which the container or task stops running.
DataCodeDataCode 可选整数值通常包含 DTSExecResult 枚举值,该枚举值指示运行该容器或任务的结果:An optional integer value that typically contains a value from the DTSExecResult enumeration that indicates the result of running the container or task:

0 – 成功0 - Success

1 - 失败1 - Failure

2 – 已完成2 - Completed

3 – 已取消3 - Canceled

日志项Log Entries

Integration ServicesIntegration Services 支持预定义事件的日志项,并提供了可用于很多 Integration ServicesIntegration Services 对象的自定义日志项。supports log entries on predefined events and provides custom log entries for many Integration ServicesIntegration Services objects. 设计器中的 “配置 SSIS 日志” SSISSSIS 对话框列出了这些事件和自定义日志项。The Configure SSIS Logs dialog box in SSISSSIS Designer lists these events and custom log entries.

下表描述了可以在发生运行时事件时启用日志项写入功能的预定义事件。The following table describes the predefined events that can be enabled to write log entries when run-time events occur. 这些日志项将应用到可执行文件、包以及包中的任务和容器。These log entries apply to executables, the package, and the tasks and containers that the package includes. 日志项的名称与引发并导致写入日志项的运行时事件的名称相同。The name of the log entry is the same as the name of the run-time event that was raised and caused the log entry to be written.

事件Events 描述Description
OnErrorOnError 出现错误时写入日志项。Writes a log entry when an error occurs.
OnExecStatusChangedOnExecStatusChanged 在提示期间挂起或恢复任务(而非容器时),写入日志项。Writes a log entry when a task (not a container) is suspended or resumed during debugging.
OnInformationOnInformation 在验证和执行可执行文件的过程中写入报告信息的日志项。Writes a log entry during the validation and execution of an executable to report information.
OnPostExecuteOnPostExecute 在可执行文件运行完成后立即写入日志项。Writes a log entry immediately after the executable has finished running.
OnPostValidateOnPostValidate 在可执行文件的验证完成时写入日志项。Writes a log entry when the validation of the executable finishes.
OnPreExecuteOnPreExecute 在可执行文件即将运行前写入日志项。Writes a log entry immediately before the executable runs.
OnPreValidateOnPreValidate 可执行文件的验证开始时写入日志项。Writes a log entry when the validation of the executable starts.
OnProgressOnProgress 在可执行文件的进度可度量时写入日志项。Writes a log entry when measurable progress is made by the executable.
OnQueryCancelOnQueryCancel 在任务处理过程中可以取消执行的任何时刻写入日志项。Writes a log entry at any juncture in the task processing where it is feasible to cancel execution.
OnTaskFailedOnTaskFailed 在任务失败时写入日志项。Writes a log entry when a task fails.
OnVariableValueChangedOnVariableValueChanged 在变量的值更改时写入日志项。Writes a log entry when the value of a variable changes.
OnWarningOnWarning 在出现警告时写入日志项。Writes a log entry when a warning occurs.
PipelineComponentTimePipelineComponentTime 对于每个数据流组件,为验证和执行的每个阶段写入日志项。For each data flow component, writes a log entry for each phase of validation and execution. 该日志条目为每个阶段指定处理时间。The log entry specifies the processing time for each phase.
诊断Diagnostic

DiagnosticExDiagnosticEx
写入提供诊断信息的日志项。Writes a log entry that provides diagnostic information.

例如,您可以在每次调用外部数据访问接口之前和之后记录消息。For example, you can log a message before and after every call to an external data provider. 有关详细信息,请参阅 包执行的疑难解答工具For more information, see Troubleshooting Tools for Package Execution.

当你想查找数据流中存在错误的列的列名称时,请记录 DiagnosticEx 事件。Log the DiagnosticEx event when you want to find the column names for columns in the data flow that have errors. 此事件将数据流沿袭映射写入到日志中。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 输出中保留空白。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.

注意:如果使用 SQL Server 日志提供程序记录 DiagnosticEx ,输出可能被截断。Note: If you log the DiagnosticEx event with the SQL Server log provider, the output may be truncated. SQL Server 日志提供程序的 消息 字段属于 nvarchar(2048) 类型。The message field of the SQL Server log provider is of type nvarchar(2048). 若要避免截断,请在记录 DiagnosticEx 事件时使用其他日志提供程序。To avoid truncation, use a different log provider when you log the DiagnosticEx event.

包和很多任务都有可以启用日志记录功能的自定义日志项。The package and many tasks have custom log entries that can be enabled for logging. 例如,发送邮件任务提供了 SendMailTaskBegin 自定义日志项,该日志项在发送邮件任务开始运行时(但在发送电子邮件消息之前)记录信息。For example, the Send Mail task provides the SendMailTaskBegin custom log entry, which logs information when the Send Mail task starts to run, but before the task sends an e-mail message. 有关详细信息,请参阅 Custom Messages for LoggingFor more information, see Custom Messages for Logging.

区分包副本Differentiating Package Copies

日志数据包括日志项所属的包的名称和 GUID。Log data includes the name and the GUID of the package to which the log entries belong. 如果通过复制现有包而创建了新的包,则现有包的名称和 GUID 也会被复制。If you create a new package by copying an existing package, the name and the GUID of the existing package are also copied. 结果,可能有两个包具有相同的 GUID 和名称,这将使您难以区分日志数据中的包。As a result, you may have two packages that have the same GUID and name, making it difficult to differentiate between the packages in the log data.

若要消除这种不明确性,应当更新新包的名称和 GUID。To eliminate this ambiguity, you should update the name and the GUID of the new packages. SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中,可以在 ID 属性中重新生成 GUID,并在“属性”窗口中更新 Name 属性的值。In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), you can regenerate the GUID in the ID property and update the value of the Name property in the Properties window. 还可以通过编程或使用 dtutil 命令提示符工具来更改 GUID 和名称。You can also change the GUID and the name programmatically, or by using the dtutil command prompt. 有关详细信息,请参阅 设置包属性dtutil 实用工具For more information, see Set Package Properties and dtutil Utility.

父日志记录选项Parent Logging Options

通常,任务以及 For 循环、Foreach 循环和序列容器的日志记录选项与包或父容器的日志记录选项匹配。Frequently, the logging options of tasks and For Loop, Foreach Loop, and Sequence containers match those of the package or a parent container. 在这种情况下,您可以将它们配置为继承其父容器的日志记录选项。In that case, you can configure them to inherit their logging options from their parent container. 例如,在包括执行 SQL 任务的 For 循环容器中,执行 SQL 任务可以使用已对 For 循环容器设置的日志记录选项。For example, in a For Loop container that includes an Execute SQL task, the Execute SQL task can use the logging options that are set on the For Loop container. 若要使用父日志记录选项,可以将容器的 LoggingMode 属性设置为 UseParentSettingTo use the parent logging options, you set the LoggingMode property of the container to UseParentSetting. 可以在 “属性” SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) 窗口中设置此属性,也可以通过 设计器中的 “配置 SSIS 日志” SSISSSIS 对话框设置此属性。You can set this property in the Properties window of SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) or through the Configure SSIS Logs dialog box in SSISSSIS Designer.

日志记录模板Logging Templates

“配置 SSIS 日志” 对话框中,还可以创建经常使用的日志记录配置并将其保存为模板,然后在多个包中使用这些模板。In the Configure SSIS Logs dialog box, you can also create and save frequently used logging configurations as templates, and then use the templates in multiple packages. 这样便于对多个包应用一致的日志记录策略,以及通过更新并应用模板来修改包的日志设置。This makes it easy to apply a consistent logging strategy across multiple packages and to modify log settings on packages by updating and then applying the templates. 这些模板存储为 XML 文件。The templates are stored in XML files.

使用“配置 SSIS 日志”对话框配置日志记录To configure logging using the Configure SSIS Logs dialog box

  1. 为包及其任务启用日志记录。Enable the package and its tasks for logging. 可以在包级、容器级以及任务级进行日志记录。Logging can occur at the package, the container, and the task level. 可以为包、容器和任务指定不同的日志。You can specify different logs for packages, containers, and tasks.

  2. 选择日志提供程序并为包添加日志。Select a log provider and add a log for the package. 可以仅在包级创建日志,任务或容器必须使用为包创建的日志之一。Logs can be created only at the package level, and a task or container must use one of the logs created for the package. 每个日志都与以下任一日志提供程序关联:文本文件、SQL Server ProfilerSQL Server ProfilerSQL ServerSQL Server、Windows 事件日志或 XML 文件。Each log is associated with one of the following log providers: Text file, SQL Server ProfilerSQL Server Profiler, SQL ServerSQL Server, Windows Event Log, or XML file. 有关详细信息,请参阅 在 SQL Server Data Tools 中启用包日志记录For more information, see Enable Package Logging in SQL Server Data Tools.

  3. 选择要在日志中捕获的事件以及每个事件的日志架构信息。Select the events and the log schema information about each event you want to capture in the log. 有关详细信息,请参阅 使用保存的配置文件配置日志记录For more information, see Configure Logging by Using a Saved Configuration File.

日志提供程序的配置Configuration of Log Provider

可以通过 SSISSSIS 设计器或以编程方式来设置属性。You can set properties through SSISSSIS Designer or programmatically.

作为在包中实现日志记录的一个步骤来创建和配置日志提供程序。A log provider is created and configured as a step in implementing logging in a package.

创建日志提供程序后,可以在 SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)的“属性”窗口中查看和修改其属性。After you create a log provider, you can view and modify its properties in the Properties window of SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT).

有关如何以编程方式设置这些属性的信息,请参阅 LogProvider 类的文档。For information about programmatically setting these properties, see the documentation for the LogProvider class.

数据流任务的日志记录Logging for Data Flow Tasks

数据流任务提供了许多可用于监视和调整性能的自定义日志项。The Data Flow task provides many custom log entries that can be used to monitor and adjust performance. 例如,您可以监视可能会导致内存泄漏的组件,或者跟踪特定组件运行所用的时间。For example, you can monitor components that might cause memory leaks, or keep track of how long it takes to run a particular component. 有关这些自定义日志项的列表和日志记录输出示例,请参阅 Data Flow TaskFor a list of these custom log entries and sample logging output, see Data Flow Task.

捕获在其中发生错误的列的名称Capture the names of columns in which errors occur

当在数据流中配置错误输出时,默认情况下错误输出仅提供在其中发生错误的列的数字标识符。When you configure an error output in the data flow, by default the error output provides only the numeric identifier of the column in which the error occurred. 有关详细信息,请参阅数据中的错误处理For more info, see Error Handling in Data.

可以通过启用日志记录并选择 DiagnosticEx 事件来查找列名称。You can find column names by enabling logging and selecting the DiagnosticEx event. 此事件将数据流沿袭映射写入到日志中。This event writes a data flow lineage map to the log. 然后可以在此沿袭映射中从其标识符查找列名称。You can then look up the column name from its identifier in this lineage map. 请注意,为了缩减日志大小, 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.

使用 PipelineComponentTime 事件Use the PipelineComponentTime Event

最有用的自定义日志项可能是 PipelineComponentTime 事件。Perhaps the most useful custom log entry is the PipelineComponentTime event. 该日志项报告数据流中的每个组件执行五个主要处理步骤中的每个步骤所用的毫秒数。This log entry reports the number of milliseconds that each component in the data flow spends on each of the five major processing steps. 下表说明了这些处理步骤。The following table describes these processing steps. Integration ServicesIntegration Services 开发人员会将这些步骤标识为 PipelineComponentdevelopers will recognize these steps as the principal methods of a PipelineComponent.

步骤Step 描述Description
验证Validate 该组件查看有效的属性值和配置设置。The component checks for valid property values and configuration settings.
PreExecutePreExecute 该组件在开始处理数据行之前执行一次性处理。The component performs one-time processing before it starts to process rows of data.
PostExecutePostExecute 该组件在处理所有数据行之后执行一次性处理。The component performs one-time processing after it has processed all rows of data.
ProcessInputProcessInput 转换或目标组件处理由上游源或转换传递的传入数据行。The transformation or destination component processes the incoming rows of data that an upstream source or transformation has passed to it.
PrimeOutputPrimeOutput 源或转换组件填充数据缓冲区,以传递给下游转换或目标组件。The source or transformation component fills the buffers of data to be passed to a downstream transformation or destination component.

启用 PipelineComponentTime 事件时, Integration ServicesIntegration Services 将针对每个组件执行的各处理步骤记录一则消息。When you enable the PipelineComponentTime event, Integration ServicesIntegration Services logs one message for each processing step performed by each component. 以下日志项显示 Integration ServicesIntegration Services CalculatedColumns 包示例记录的消息的子集。The following log entries show a subset of the messages that the Integration ServicesIntegration Services CalculatedColumns package sample logs:

The component "Calculate LineItemTotalCost" (3522) spent 356 milliseconds in ProcessInput.

The component "Sum Quantity and LineItemTotalCost" (3619) spent 79 milliseconds in ProcessInput.

The component "Calculate Average Cost" (3662) spent 16 milliseconds in ProcessInput.

The component "Sort by ProductID" (3717) spent 125 milliseconds in ProcessInput.

The component "Load Data" (3773) spent 0 milliseconds in ProcessInput.

The component "Extract Data" (3869) spent 688 milliseconds in PrimeOutput filling buffers on output "OLE DB Source Output" (3879).

The component "Sum Quantity and LineItemTotalCost" (3619) spent 141 milliseconds in PrimeOutput filling buffers on output "Aggregate Output 1" (3621).

The component "Sort by ProductID" (3717) spent 16 milliseconds in PrimeOutput filling buffers on output "Sort Output" (3719).

下列日志项显示数据流任务在下列步骤中消耗了大多数时间,如下所示(按降序排序):These log entries show that the data flow task spent the most time on the following steps, shown here in descending order:

  • 名为“Extract Data”的 OLE DB 源在加载数据期间耗时 688 毫秒The OLE DB source that is named "Extract Data" spent 688 ms. 加载数据。loading data.

  • 名为“Calculate LineItemTotalCost”的派生列转换The Derived Column transformation that is named "Calculate LineItemTotalCost" spent 356 ms. 在对传入行执行计算期间耗时 356 毫秒。performing calculations on incoming rows.

  • 名为“Sum Quantity and LineItemTotalCost”的聚合转换在执行计算和将数据传递到下一转换期间共耗时 220 毫秒,其中有 141 毫秒用于 PrimeOutput,有 79 毫秒用于 ProcessInput。The Aggregate transformation that is named "Sum Quantity and LineItemTotalCost" spent a combined 220 ms-141 in PrimeOutput and 79 in ProcessInput-performing calculations and passing the data to the next transformation.

在 SQL Server Data Tools 中启用包日志记录Enable Package Logging in SQL Server Data Tools

本过程介绍如何将日志添加到包中,如何配置包级日志记录,以及如何将日志记录配置保存为 XML 文件。This procedure describes how to add logs to a package, configure package-level logging, and save the logging configuration to an XML file. 您只能在包级添加日志,但包不必执行日志记录,就可以在包所包括的容器中启用日志记录。You can add logs only at the package level, but the package does not have to perform logging to enable logging in the containers that the package includes.

重要

如果您将 Integration ServicesIntegration Services 项目部署到 Integration ServicesIntegration Services 服务器,则为包执行设置的日志记录级别优先于使用 SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)配置的包日志记录。If you deploy the Integration ServicesIntegration Services project to the Integration ServicesIntegration Services server, the logging level that you set for the package execution overrides the package logging that you configure using SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT).

默认情况下,包中的容器与其父容器使用相同的日志记录配置。By default, the containers in the package use the same logging configuration as their parent container. 有关为各个容器设置日志记录选项的信息,请参阅 使用保存的配置文件配置日志记录For information about setting logging options for individual containers, see Configure Logging by Using a Saved Configuration File.

在包中启用日志记录To enable logging in a package

  1. SQL Server Data ToolsSQL Server Data Tools中,打开包含所需包的 Integration ServicesIntegration Services 项目。In SQL Server Data ToolsSQL Server Data Tools, open the Integration ServicesIntegration Services project that contains the package you want.

  2. SSIS 菜单上,单击 “日志记录”On the SSIS menu, click Logging.

  3. “提供程序类型” 列表中,选择一个日志提供程序,然后单击 “添加”Select a log provider in the Provider type list, and then click Add.

  4. 在“配置”列中,选择连接管理器或单击“<新建连接>”以为日志提供程序新建一个适当类型的连接管理器 。In the Configuration column, select a connection manager or click <New connection> to create a new connection manager of the appropriate type for the log provider. 根据所选提供程序的不同,可以使用下列某个连接管理器:Depending on the selected provider, use one of the following connection managers:

    • 对于文本文件,请使用文件连接管理器。For Text files, use a File connection manager. 有关详细信息,请参阅 File Connection ManagerFor more information, see File Connection Manager

    • 对于 SQL Server ProfilerSQL Server Profiler,请使用文件连接管理器。For SQL Server ProfilerSQL Server Profiler, use a File connection manager.

    • 对于 SQL ServerSQL Server,请使用 OLE DB 连接管理器。For SQL ServerSQL Server, use an OLE DB connection manager. 有关详细信息,请参阅 OLE DB Connection ManagerFor more information, see OLE DB Connection Manager.

    • 对于 Windows 事件日志,无需执行任何操作。For Windows Event Log, do nothing. SSISSSIS 会自动创建日志。automatically creates the log.

    • 对于 XML 文件,请使用文件连接管理器。For XML files, use a File connection manager.

  5. 对于要在包中使用的每个日志,请重复步骤 3 和步骤 4。Repeat steps 3 and 4 for each log to use in the package.

    备注

    一个包可以使用多个同一类型的日志。A package can use more than one log of each type.

  6. 还可以选中包级复选框,选择要用于包级日志记录的日志,然后单击“详细信息” 选项卡。Optionally, select the package-level check box, select the logs to use for package-level logging, and then click the Details tab.

  7. “详细信息” 选项卡上,选择 “事件” 将记录所有日志项,清除 “事件” 可以选择单个事件。On the Details tab, select Events to log all log entries, or clear Events to select individual events.

  8. 还可以单击 “高级” 指定要记录的信息。Optionally, click Advanced to specify which information to log.

    备注

    默认情况下会记录所有信息。By default, all information is logged.

  9. 在“详细信息” 选项卡上,单击“保存” 。On the Details tab, click Save. 将显示 “另存为” 对话框。The Save As dialog box appears. 找到要将日志记录配置保存到的文件夹,为新的日志配置键入文件名,然后单击 “保存”Locate the folder in which to save the logging configuration, type a file name for the new log configuration, and then click Save.

  10. 单击“确定” 。Click OK.

  11. 若要保存更新后的包,请单击 “文件” 菜单上的 “保存选定项”To save the updated package, click Save Selected Items on the File menu.

“配置 SSIS 日志”对话框Configure SSIS Logs Dialog Box

使用 “配置 SSIS 日志” 对话框可以定义包的日志记录选项。Use the Configure SSIS Logs dialog box to define logging options for a package.

您希望做什么?What do you want to do?

  1. 打开“配置 SSIS 日志”对话框Open the Configure SSIS Logs Dialog Box

  2. 配置“容器”窗格中的选项Configure the Options in the Containers Pane

  3. 配置“提供程序和日志”选项卡上的选项Configure the Options on the Providers and Logs Tab

  4. 配置“详细信息”选项卡上的选项Configure the Options on the Details Tab

打开“配置 SSIS 日志”对话框Open the Configure SSIS Logs Dialog Box

打开“配置 SSIS 日志”对话框To open the Configure SSIS Logs dialog box

  • SSISSSIS 设计器的 SSIS 菜单上,单击 “日志记录”In the SSISSSIS Designer, click Logging on the SSIS menu.

配置“容器”窗格中的选项Configure the Options in the Containers Pane

可以使用 “配置 SSIS 日志” 对话框的 “容器” 窗格,为包及其容器启用日志记录。Use the Containers pane of the Configure SSIS Logs dialog box to enable the package and its containers for logging.

选项Options

“配置 SSIS 日志”Containers
选中层次结构视图中的该复选框可以为日志记录启用包和包容器:Select the check boxes in the hierarchical view to enable the package and its containers for logging:

  • 如果清除此复选框,则不会为日志记录启用容器。If cleared, the container is not enabled for logging. 选中此复选框将启用日志记录。Select to enable logging.

  • 如果此复选框为灰色,则容器将使用其父容器的日志记录选项。If dimmed, the container uses the logging options of its parent. 此选项对包不可用。This option is not available for the package.

  • 如果选中此复选框,那么容器将定义自己的日志记录选项。If checked, the container defines its own logging options.

如果容器为灰色,而您想要设置该容器的日志记录选项,请单击对应复选框两次。If a container is dimmed and you want to set logging options on the container, click its check box twice. 第一次单击将清除该复选框,第二次单击将选中该复选框,这样您就可以选择要使用的日志提供程序以及要记录的信息。The first click clears the check box, and the second click selects it, enabling you to choose the log providers to use and select the information to log.

配置“提供程序和日志”选项卡上的选项Configure the Options on the Providers and Logs Tab

可以使用“配置 SSIS 日志” 对话框的“提供程序和日志” 选项卡,创建和配置用于捕获运行时事件的日志。Use the Providers and Logs tab of the Configure SSIS Logs dialog box to create and configure logs for capturing run-time events.

选项Options

提供程序类型Provider type
从列表中选择日志提供程序的类型。Select a type of log provider from the list.

“添加”Add
将指定类型的日志添加到包的日志提供程序集合中。Add a log of the specified type to the collection of log providers of the package.

名称Name
通过使用复选框,可以为在“配置 SSIS 日志” 对话框的“容器” 窗格中选择的容器或任务启用或禁用日志。Enable or disable logs for containers or tasks selected in the Containers pane of the Configure SSIS Logs dialog box, by using the check boxes. 名称字段是可编辑的。The name field is editable. 可以使用提供程序的默认名称,也可以键入唯一的描述性名称。Use the default name for the provider, or type a unique descriptive name.

DescriptionDescription
说明字段是可编辑的。The description field is editable. 可以单击该字段,然后修改日志的默认说明。Click and then modify the default description of the log.

ConfigurationConfiguration
在列表中选择一个现有的连接管理器或单击<“新建连接...”> 以创建新的连接管理器。Select an existing connection manager in the list, or click <New connection...> to create a new connection manager. 根据日志提供程序的类型,您可以配置 OLE DB 连接管理器或文件连接管理器。Depending on the type of log provider, you can configure an OLE DB connection manager or a File connection manager. MicrosoftMicrosoft Windows 事件日志的日志提供程序不需要任何连接。The log provider for the MicrosoftMicrosoft Windows Event Log requires no connection.

相关主题:OLE DB 连接管理器管理器、文件连接管理器Related Topics: OLE DB Connection Manager manager, File Connection Manager

删除Delete
选择一个日志提供程序,然后单击“删除” 。Select a log provider and then click Delete.

配置“详细信息”选项卡上的选项Configure the Options on the Details Tab

可以使用 “配置 SSIS 日志” 对话框的 “详细信息” 选项卡,指定要启用日志记录的事件以及要记录的详细信息。Use the Details tab of the Configure SSIS Logs dialog box to specify the events to enable for logging and the information details to log. 所选的信息适用于包中的所有日志提供程序。The information that you select applies to all the log providers in the package. 例如,无法将一些信息写入 SQL ServerSQL Server 实例,而在文本文件中写入另外一些信息。For example, you cannot write some information to the SQL ServerSQL Server instance and different information to a text file.

选项Options

事件Events
为事件启用或禁用日志记录功能。Enable or disable events for logging.

DescriptionDescription
查看事件的说明。View a description of the event.

高级Advanced
选中或清除要记录的事件,以及选中或清除要为每个事件记录的信息。Select or clear events to log, and select or clear information to log for each event. 单击 “基本” 可以隐藏除事件列表之外的所有日志记录详细信息。Click Basic to hide all logging details, except the list of events. 日志记录可以包含以下信息:The following information is available for logging:

ReplTest1Value 描述Description
ComputerComputer 发生所记录事件的计算机的名称。The name of the computer on which the logged event occurred.
“运算符”Operator 启动包的人员的用户名。The user name of the person who started the package.
SourceNameSourceName 发生所记录事件的包、容器或任务的名称。The name of the package, container, or task in which the logged event occurred.
SourceIDSourceID 发生所记录事件的包、容器或任务的全局唯一标识符 (GUID)。The global unique identifier (GUID) of the package, container, or task in which the logged event occurred.
ExecutionIDExecutionID 包执行实例的全局唯一标识符。The global unique identifier of the package execution instance.
MessageTextMessageText 与日志项关联的消息。A message associated with the log entry.
DataBytesDataBytes 保留供将来使用。Reserved for future use.

“基本”Basic
选中或清除要记录的事件。Select or clear events to log. 选择此选项将隐藏除事件列表之外的日志记录详细信息。This option hides logging details except the list of events. 默认情况下,如果选择某事件,则会为该事件选择所有日志记录详细信息。If you select an event, all logging details are selected for the event by default. 单击 “高级” 将显示所有日志记录详细信息。Click Advanced to show all logging details.

加载Load
指定要用作设置日志记录选项的模板的现有 XML 文件。Specify an existing XML file to use as a template for setting logging options.

保存Save
将配置详细信息以模板形式保存到 XML 文件。Save configuration details as a template to an XML file.

使用保存的配置文件配置日志记录Configure Logging by Using a Saved Configuration File

本过程介绍如何通过加载以前保存的日志记录配置文件来为包中的新容器配置日志记录。This procedure describes how to configure logging for new containers in a package by loading a previously saved logging configuration file.

默认情况下,包中的所有容器与其父容器使用相同的日志记录配置。By default, all containers in a package use the same logging configuration as their parent container. 例如,Foreach 循环中的任务使用与 Foreach 循环相同的日志记录配置。For example, the tasks in a Foreach Loop use the same logging configuration as the Foreach Loop.

为容器配置日志记录To configure logging for a container

  1. SQL Server Data ToolsSQL Server Data Tools中,打开包含所需包的 Integration ServicesIntegration Services 项目。In SQL Server Data ToolsSQL Server Data Tools, open the Integration ServicesIntegration Services project that contains the package you want.

  2. SSIS 菜单上,单击 “日志记录”On the SSIS menu, click Logging.

  3. 展开包的树视图,并选择要配置的容器。Expand the package tree view and select the container to configure.

  4. “提供程序和日志” 选项卡上,选择要用于该容器的日志。On the Providers and Logs tab, select the logs to use for the container.

    备注

    只能在包级创建日志。You can create logs only at the package level. 有关详细信息,请参阅 在 SQL Server Data Tools 中启用包日志记录For more information, see Enable Package Logging in SQL Server Data Tools.

  5. 单击 “详细信息” 选项卡,单击 “加载”Click the Details tab and click Load.

  6. 找到要使用的日志记录配置文件,并单击 “打开”Locate the logging configuration file you want to use and click Open.

  7. (可选)通过在 “事件” 列中选中其复选框,选择要记录的其他日志项。Optionally, select a different log entry to log by selecting its check box in the Events column. 单击 “高级” 可以选择要为此项记录的信息类型。Click Advanced to select the type of information to log for this entry.

    备注

    新容器可以包含最初用于创建日志记录配置的容器中所没有的其他日志项。The new container may include additional log entries that are not available for the container originally used to create the logging configuration. 您必须手动选择这些其他日志项,才能对它们进行记录。These additional log entries must be selected manually if you want them to be logged.

  8. 若要保存日志记录配置的更新后的版本,请单击 “保存”To save the updated version of the logging configuration, click Save.

  9. 若要保存更新后的包,请单击 “文件” 菜单上的 “保存选定项”To save the updated package, click Save Selected Items on the File menu.

在 SSIS 服务器上启用包执行的日志记录Enable Logging for Package Execution on the SSIS Server

本主题介绍在运行已经部署到 Integration ServicesIntegration Services 服务器的包时,如何设置或更改该包的日志记录级别。This topic describes how to set or change the logging level for a package when you run a package that you have deployed to the Integration ServicesIntegration Services server. 在运行包时设置的日志记录级别将覆盖你在 SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中设计时配置的包日志记录。The logging level you set when you run the package overrides the package logging you configure at design time in SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT). 有关详细信息,请参阅在 SQL Server Data Tools 中启用包日志记录See Enable Package Logging in SQL Server Data Tools for more information.

在 SQL Server“服务器属性”中,“服务器日志记录级别”属性下,可以选择默认服务器范围内的日志记录级别。 In SQL Server Server Properties, under the Server logging level property, you can select a default server-wide logging level. 可以从本主题中介绍的内置日志记录中选择一项,或者选择现有的自定义日志记录级别。You can pick from one of the built-in logging levels described in this topic, or you can pick an existing customized logging level. 默认情况下,所选的日志记录级别适用于部署到 SSIS 目录的所有包。The selected logging level applies by default to all packages deployed to the SSIS Catalog. 同时也默认适用于运行 SSIS 包的 SQL 代理作业步骤。It also applies by default to a SQL Agent job step that runs an SSIS package.

此外,也可使用下列方法之一对单个包指定日志记录级别。You can also specify the logging level for an individual package by using one of the following methods. 本主题涵盖第一种方法。This topic covers the first method.

通过使用“执行包”对话框设置包的日志记录级别Set the logging level for a package by using the Execute Package dialog box

  1. SQL Server Management StudioSQL Server Management Studio中,导航到对象资源管理器中的包。In SQL Server Management StudioSQL Server Management Studio, navigate to the package in Object Explorer.

  2. 右键单击包,然后选择“执行”。 Right-click the package and select Execute.

  3. “执行包” 对话框中,选择 “高级” 选项卡。Select the Advanced tab in the Execute Package dialog box.

  4. “日志记录级别” 下,选择日志记录级别。Under Logging level, select the logging level. 本主题包含可用值的说明。This topic contains a description of available values.

  5. 完成所有其他包配置,然后单击 “确定” 运行该包。Complete any other package configurations, then click OK to run the package.

选择日志记录级别Select a logging level

以下内置日志记录级别可用。The following built-in logging levels are available. 还可以选择现有的自定义日志记录级别。You can also select an existing customized logging level. 本主题包含自定义日志记录级别的说明。This topic contains a description of customized logging levels.

“日志记录级别”Logging Level 描述Description
NoneNone 关闭日志记录。Logging is turned off. 仅记录包执行状态。Only the package execution status is logged.
“基本”Basic 除了自定义事件和诊断事件之外,记录其余所有事件。All events are logged, except custom and diagnostic events. 这是默认值。This is the default value.
运行时沿袭RuntimeLineage 收集跟踪数据流中的沿袭信息所需的数据。Collects the data required to track lineage information in the data flow. 可以分析此沿袭信息以映射任务之间的沿袭关系。You can parse this lineage information to map the lineage relationship between tasks. 使用此信息,ISV 和开发人员可以构建自定义沿袭映射工具。ISVs and developers can build custom lineage mapping tools with this information.
性能Performance 仅记录性能统计信息、OnError 和 OnWarning 事件。Only performance statistics, and OnError and OnWarning events, are logged.

“执行性能” 报表显示包数据流组件的活动时间和总时间。The Execution Performance report displays Active Time and Total Time for package data flow components. 仅当上次包执行的日志记录级别设置为 “性能”“详细” 时,此信息才可用。This information is available when the logging level of the last package execution was set to Performance or Verbose. 有关详细信息,请参阅 Reports for the Integration Services ServerFor more information, see Reports for the Integration Services Server.

catalog.execution_component_phases 视图显示数据流组件在执行的每个阶段的开始时间和结束时间。The catalog.execution_component_phases view displays the start and end times for the data flow components, for each phase of an execution. 仅当包执行的日志记录级别设置为 “性能”“详细” 时,此视图才会为这些组件显示以上信息。This view displays this information for these components only when the logging level of the package execution is set to Performance or Verbose.
“详细”Verbose 记录所有事件,包括自定义事件和诊断事件。All events are logged, including custom and diagnostic events.

自定义事件包括 Integration ServicesIntegration Services 任务记录的那些事件。Custom events include those events that are logged by Integration ServicesIntegration Services tasks. 有关自定义事件的详细信息,请参阅 Custom Messages for LoggingFor more information about custom events, see Custom Messages for Logging.

诊断事件的一个例子就是“DiagnosticEx” 事件。An example of a diagnostic event is the DiagnosticEx event. 每当执行包任务执行子包时,此事件均将捕获传递给子包的参数值。Whenever an Execute Package task executes a child package, this event captures the parameter values passed to child packages.

“DiagnosticEx” 事件还有助于获取其中出现行级错误的列的名称。The DiagnosticEx event also helps you to get the names of columns in which row-level errors occur. 此事件将数据流沿袭映射写入到日志中。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.

每当数据流组件向下游组件发送数据时, catalog.execution_data_statistics 视图就会显示一行。The catalog.execution_data_statistics view displays a row each time a data flow component sends data to a downstream component, for a package execution. 日志记录级别必须设置为 “详细” ,才能在该视图中捕获此信息。The logging level must be set to Verbose to capture this information in the view.

使用“自定义日志记录级别管理”对话框来创建和管理自定义日志记录级别Create and manage customized logging levels by using the Customized Logging Level Management dialog box

可以创建只收集想要的统计信息和事件的自定义日志记录级别。You can create customized logging levels that collect only the statistics and events that you want. 还可以选择捕获事件上下文,包括变量值、连接字符串和组件属性。Optionally you can also capture the context of events, which includes variable values, connection strings, and component properties. 运行包时,可在任何可以选择内置日志记录级别的位置处,选择自定义日志记录级别。When you run a package, you can select a customized logging level wherever you can select a built-in logging level.

提示

若要捕获包变量的值,必须将该变量的 IncludeInDebugDump 属性设置为 TrueTo capture the values of package variables, the IncludeInDebugDump property of the variables must be set to True.

  1. 要创建和管理自定义日志记录级别,在 SQL Server Management StudioSQL Server Management Studio 中,右键单击 SSISDB 数据库并选择“自定义日志记录级别”以打开“自定义日志记录级别管理”对话框。 To create and manage customized logging levels, in SQL Server Management StudioSQL Server Management Studio, right-click on the SSISDB database and select Customized Logging Level to open the Customized Logging Level Management dialog box. “自定义日志记录级别” 列表包含所有现有的自定义日志记录级别。The Customized Logging Levels list contains all the existing customized logging levels.

  2. 创建 新的自定义日志记录级别,请单击“创建” ,然后提供名称和描述。To create a new customized logging level, click Create, and then provide a name and description. 在“统计信息” 和“事件” 选项卡上,选择想要收集的统计信息和事件。On the Statistics and Events tabs, select the statistics and events that you want to collect. 在“事件” 选项卡上,可以选择对单个事件选择“包括上下文” 。On the Events tab, optionally select Include Context for individual events. 然后单击“保存” 。Then click Save.

  3. 更新 现有的自定义日志记录级别,请在列表中选中并重新配置它,然后单击“保存” 。To update an existing customized logging level, select it in the list, reconfigure it, and then click Save.

  4. 删除 现有的自定义日志记录级别,请在列表中选中它,然后单击“删除” 。To delete an existing customized logging level, select it in the list, and then click Delete.

自定义日志记录级别的权限。Permissions for customized logging levels.

  • SSISDB 数据库的所有用户都可以查看自定义日志记录级别,并可在运行包时选择一个自定义日志记录级别。All users of the SSISDB database can see customized logging levels and select a customized logging level when they run packages.

  • 只有 ssis_admin 或 sysadmin 角色中的用户可以创建、更新或删除自定义日志记录级别。Only users in the ssis_admin or sysadmin role can create, update, or delete customized logging levels.

Custom Messages for LoggingCustom Messages for Logging

SQL Server Integration Services 提供了一组丰富的自定义事件,可以用来写入包和很多任务的日志项。SQL Server Integration Services provides a rich set of custom events for writing log entries for packages and many tasks. 使用这些项可以记录预定义的事件或用户定义的消息,供随后分析时使用,从而将有关执行进度、结果和问题的详细信息保存下来。You can use these entries to save detailed information about execution progress, results, and problems by recording predefined events or user-defined messages for later analysis. 例如,可以记录大容量插入的开始和结束时间,从而找出包运行时的性能问题。For example, you can record when a bulk insert begins and ends to identify performance issues when the package runs.

与对包和所有容器及任务可用的标准日志记录事件集相比,自定义日志项是一组不同的项。The custom log entries are a different set of entries than the set of standard logging events that are available for packages and all containers and tasks. 您可以根据需要使用自定义日志项来捕获有关包中特定任务的有用信息。The custom log entries are tailored to capture useful information about a specific task in a package. 例如,执行 SQL 任务的一个自定义日志项可以在日志中记录该任务所执行的 SQL 语句。For example, one of the custom log entries for the Execute SQL task records the SQL statement that the task executes in the log.

所有日志项都包括日期和时间信息,包括在包开始和完成时自动写入的日志项。All log entries include date and time information, including the log entries that are automatically written when a package begins and finishes. 很多日志事件都会在日志中写入多个项。Many of the log events write multiple entries to the log. 这通常发生在事件有不同阶段时。This typically occurs when the event has different phases. 例如, ExecuteSQLExecutingQuery 日志事件写入三项:在任务获得与数据库的连接之后写入一项,在任务开始准备 SQL 语句之后写入另一项,并在执行完 SQL 语句之后再写入一项。For example, the ExecuteSQLExecutingQuery log event writes three entries: one entry after the task acquires a connection to the database, another after the task starts to prepare the SQL statement, and one more after the execution of the SQL statement is completed.

以下 Integration ServicesIntegration Services 对象有自定义日志项:The following Integration ServicesIntegration Services objects have custom log entries:

Package

大容量插入任务Bulk Insert Task

数据流任务Data Flow Task

执行 DTS 2000 任务Execute DTS 2000 Task

执行进程任务Execute Process Task

执行 SQL 任务Execute SQL Task

文件系统任务File System Task

FTP 任务FTP Task

消息队列任务Message Queue Task

脚本任务Script Task

发送邮件任务Send Mail Task

传输数据库任务Transfer Database Task

传输错误消息任务Transfer Error Messages Task

传输作业任务Transfer Jobs Task

传输登录名任务Transfer Logins Task

传输主存储过程任务Transfer Master Stored Procedures Task

传输 SQL Server 对象任务Transfer SQL Server Objects Task

Web 服务任务Web Services Task

WMI 数据读取器任务WMI Data Reader Task

WMI 事件观察器任务WMI Event Watcher Task

XML 任务XML Task

日志项Log Entries

Package

下表列出了包的自定义日志项。The following table lists the custom log entries for packages.

日志项Log entry 描述Description
PackageStartPackageStart 指示包开始运行。Indicates that the package began to run. 此日志项自动写入日志。This log entry is automatically written to the log. 无法排除它。You cannot exclude it.
PackageEndPackageEnd 指示包已完成。Indicates that the package completed. 此日志项自动写入日志。This log entry is automatically written to the log. 无法排除它。You cannot exclude it.
诊断Diagnostic 提供影响包执行的系统配置的相关信息,例如,可并发运行的可执行文件数。Provides information about the system configuration that affects package execution such as the number executables that can be run concurrently.

Diagnostic 日志项还包括调用外部数据访问接口之前和之后的项。The Diagnostic log entry also includes before and after entries for calls to external data providers.

大容量插入任务Bulk Insert Task

下表列出了大容量插入任务的自定义日志项。The following table lists the custom log entries for the Bulk Insert task.

日志项Log entry 描述Description
DTSBulkInsertTaskBeginDTSBulkInsertTaskBegin 指示大容量插入开始。Indicates that the bulk insert began.
DTSBulkInsertTaskEndDTSBulkInsertTaskEnd 指示大容量插入完成。Indicates that the bulk insert finished.
DTSBulkInsertTaskInfosDTSBulkInsertTaskInfos 提供有关任务的说明性信息。Provides descriptive information about the task.

数据流任务Data Flow Task

下表列出了数据流任务的自定义日志项。The following table lists the custom log entries for the Data Flow task.

日志项Log entry 描述Description
BufferSizeTuningBufferSizeTuning 指示数据流任务更改了缓冲区的大小。Indicates that the Data Flow task changed the size of the buffer. 日志条目描述了大小更改的原因,并列出了临时的新缓冲区大小。The log entry describes the reasons for the size change and lists the temporary new buffer size.
OnPipelinePostEndOfRowsetOnPipelinePostEndOfRowset 表示组件已经给出它的行集结束信号,该信号由对 ProcessInput 方法的最后一次调用设置。Denotes that a component has been given its end-of-rowset signal, which is set by the last call of the ProcessInput method. 对于数据流中处理输入的每个组件,都会写入一项。An entry is written for each component in the data flow that processes input. 该项包括组件的名称。The entry includes the name of the component.
OnPipelinePostPrimeOutputOnPipelinePostPrimeOutput 指示组件已经完成它对 PrimeOutput 方法的最后一次调用。Indicates that the component has completed its last call to the PrimeOutput method. 取决于数据流,可能写入多个日志条目。Depending on the data flow, multiple log entries may be written. 如果组件是源组件,这意味着该组件已经完成对行的处理。If the component is a source, this means that the component has finished processing rows.
OnPipelinePreEndOfRowsetOnPipelinePreEndOfRowset 指示组件将要接收它的行集结束信号,该信号由对 ProcessInput 方法的最后一次调用设置。Indicates that a component is about to receive its end-of-rowset signal, which is set by the last call of the ProcessInput method. 对于数据流中处理输入的每个组件,都会写入一项。An entry is written for each component in the data flow that processes input. 该项包括组件的名称。The entry includes the name of the component.
OnPipelinePrePrimeOutputOnPipelinePrePrimeOutput 指示组件将从 PrimeOutput 方法接收它的调用。Indicates that the component is about to receive its call from the PrimeOutput method. 取决于数据流,可能写入多个日志条目。Depending on the data flow, multiple log entries may be written.
OnPipelineRowsSentOnPipelineRowsSent 报告对 ProcessInput 方法的调用为组件输入所提供的行数。Reports the number of rows provided to a component input by a call to the ProcessInput method. 此日志条目包括组件名。The log entry includes the component name.
PipelineBufferLeakPipelineBufferLeak 提供在缓冲区管理器退出之后使缓冲区保持活动状态的任何组件的相关信息。Provides information about any component that kept buffers alive after the buffer manager goes away. 这意味着缓冲区资源未释放,并且可能导致内存泄漏。This means that buffers resources were not released and may cause memory leaks. 日志条目提供组件的名称和缓冲区的 ID。The log entry provides the name of the component and the ID of the buffer.
PipelineExecutionPlanPipelineExecutionPlan 报告数据流的执行计划。Reports the execution plan of the data flow. 它提供有关缓冲区将如何发送到组件的信息。It provides information about how buffers will be sent to components. 此信息与 PipelineExecutionTrees 项组合,一起描述在任务中所发生的事情。This information, in combination with the PipelineExecutionTrees entry, describes what is occurring in the task.
PipelineExecutionTreesPipelineExecutionTrees 报告数据流中的布局的执行树。Reports the execution trees of the layout in the data flow. 数据流引擎的计划程序使用这些树生成数据流的执行计划。The scheduler of the data flow engine use the trees to build the execution plan of the data flow.
PipelineInitializationPipelineInitialization 提供有关任务的初始化信息。Provides initialization information about the task. 此信息包括要用来临时存储 BLOB 数据、默认缓冲区大小和缓冲区行数的目录。This information includes the directories to use for temporary storage of BLOB data, the default buffer size, and the number of rows in a buffer. 取决于数据流任务的配置,可能写入多个日志条目。Depending on the configuration of the Data Flow task, multiple log entries may be written.

执行 DTS 2000 任务Execute DTS 2000 Task

下表列出了执行 DTS 2000 任务的自定义日志项。The following table lists the custom log entries for the Execute DTS 2000 task.

日志项Log entry 描述Description
ExecuteDTS80PackageTaskBeginExecuteDTS80PackageTaskBegin 指示任务开始运行 DTS 2000 包。Indicates that the task began to run a DTS 2000 package.
ExecuteDTS80PackageTaskEndExecuteDTS80PackageTaskEnd 指示任务已完成。Indicates that the task finished.

注意:DTS 2000 包可能会在任务结束后继续运行。Note: The DTS 2000 package may continue to run after the task ends.
ExecuteDTS80PackageTaskTaskInfoExecuteDTS80PackageTaskTaskInfo 提供有关任务的说明性信息。Provides descriptive information about the task.
ExecuteDTS80PackageTaskTaskResultExecuteDTS80PackageTaskTaskResult 报告该任务所运行的 DTS 2000 包的执行结果。Reports the execution result of the DTS 2000 package that the task ran.

执行进程任务Execute Process Task

下表列出了执行进程任务的自定义日志项。The following table lists the custom log entries for the Execute Process task.

日志项Log entry 描述Description
ExecuteProcessExecutingProcessExecuteProcessExecutingProcess 提供有关进程的信息,该进程运行此任务按配置要求要运行的可执行文件。Provides information about the process of running the executable that the task is configured to run.

写入两个日志条目。Two log entries are written. 一个日志项包含有关任务所运行的可执行文件的名称和位置的信息,另一个则记录从可执行文件退出的信息。One contains information about the name and location of the executable that the task runs, and the other records the exit from the executable.
ExecuteProcessVariableRoutingExecuteProcessVariableRouting 提供有关哪些变量被路由到可执行文件的输入和输出的信息。Provides information about which variables are routed to the input and outputs of the executable. 将为 stdin(输入)、stdout(输出)和 stderr(错误输出)写入日志条目。Log entries are written for stdin (the input), stdout (the output), and stderr (the error output).

执行 SQL 任务Execute SQL Task

下表介绍了执行 SQL 任务的自定义日志项。The following table describes the custom log entry for the Execute SQL task.

日志项Log entry 描述Description
ExecuteSQLExecutingQueryExecuteSQLExecutingQuery 提供有关 SQL 语句的执行阶段的信息。Provides information about the execution phases of the SQL statement. 在任务获得与数据库的连接时、任务开始准备 SQL 语句时以及执行完 SQL 语句之后写入日志项。Log entries are written when the task acquires connection to the database, when the task starts to prepare the SQL statement, and after the execution of the SQL statement is completed. 准备阶段的日志条目包括任务所使用的 SQL 语句。The log entry for the prepare phase includes the SQL statement that the task uses.

文件系统任务File System Task

下表介绍了文件系统任务的自定义日志项。The following table describes the custom log entry for the File System task.

日志项Log entry 描述Description
FileSystemOperationFileSystemOperation 报告任务所执行的操作。Reports the operation that the task performs. 在文件系统操作开始时写入日志项,日志项包括有关源和目标的信息。The log entry is written when the file system operation starts and includes information about the source and destination.

FTP 任务FTP Task

下表列出了 FTP 任务的自定义日志项。The following table lists the custom log entries for the FTP task.

日志项Log entry 描述Description
FTPConnectingToServerFTPConnectingToServer 指示任务已启动与 FTP 服务器的连接。Indicates that the task initiated a connection to the FTP server.
FTPOperationFTPOperation 报告任务所执行的 FTP 操作的开始及其类型。Reports the beginning of and the type of FTP operation that the task performs.

消息队列任务Message Queue Task

下表列出了消息队列任务的自定义日志项。The following table lists the custom log entries for the Message Queue task.

日志项Log entry 描述Description
MSMQAfterOpenMSMQAfterOpen 指示任务已完成打开消息队列的操作。Indicates that the task finished opening the message queue.
MSMQBeforeOpenMSMQBeforeOpen 指示任务开始打开消息队列。Indicates that the task began to open the message queue.
MSMQBeginReceiveMSMQBeginReceive 指示任务开始接收消息。Indicates that the task began to receive a message.
MSMQBeginSendMSMQBeginSend 指示任务开始发送消息。Indicates that the task began to send a message.
MSMQEndReceiveMSMQEndReceive 指示任务完成接收消息。Indicates that the task finished receiving a message.
MSMQEndSendMSMQEndSend 指示任务完成发送消息的操作。Indicates that the task finished sending a message
MSMQTaskInfoMSMQTaskInfo 提供有关任务的说明性信息。Provides descriptive information about the task.
MSMQTaskTimeOutMSMQTaskTimeOut 指示任务已超时。Indicates that the task timed out.

脚本任务Script Task

下表介绍了脚本任务的自定义日志项。The following table describes the custom log entry for the Script task.

日志项Log entry 描述Description
ScriptTaskLogEntryScriptTaskLogEntry 报告在脚本中实现日志记录的结果。Reports the results of implementing logging in the script. 在每次调用 Log 对象的 Dts 方法时将写入日志项。A log entry is written for each call to the Log method of the Dts object. 代码运行时将写入日志项。The entry is written when the code is run. 有关详细信息,请参阅 Logging in the Script TaskFor more information, see Logging in the Script Task.

发送邮件任务Send Mail Task

下表列出了发送邮件任务的自定义日志项。The following table lists the custom log entries for the Send Mail task.

日志项Log entry 描述Description
SendMailTaskBeginSendMailTaskBegin 指示任务开始发送电子邮件。Indicates that the task began to send an e-mail message.
SendMailTaskEndSendMailTaskEnd 指示任务已发送完电子邮件。Indicates that the task finished sending an e-mail message.
SendMailTaskInfoSendMailTaskInfo 提供有关任务的说明性信息。Provides descriptive information about the task.

传输数据库任务Transfer Database Task

下表列出了传输数据库任务的自定义日志项。The following table lists the custom log entries for the Transfer Database task.

日志项Log entry 描述Description
SourceDBSourceDB 指定任务所复制的数据库。Specifies the database that the task copied.
SourceSQLServerSourceSQLServer 指定从中复制数据库的计算机。Specifies the computer from which the database was copied.

传输错误消息任务Transfer Error Messages Task

下表列出了传输错误消息任务的自定义日志项。The following table lists the custom log entries for the Transfer Error Messages task.

日志项Log entry 描述Description
TransferErrorMessagesTaskFinishedTransferringObjectsTransferErrorMessagesTaskFinishedTransferringObjects 指示任务已完成传输错误消息的操作。Indicates that the task finished transferring error messages.
TransferErrorMessagesTaskStartTransferringObjectsTransferErrorMessagesTaskStartTransferringObjects 指示任务已开始传输错误消息。Indicates that the task started to transfer error messages.

传输作业任务Transfer Jobs Task

下表列出传输作业任务的自定义日志项。The following table lists the custom log entries for the Transfer Jobs task.

日志项Log entry 描述Description
TransferJobsTaskFinishedTransferringObjectsTransferJobsTaskFinishedTransferringObjects 指示任务已完成传输 SQL ServerSQL Server 代理作业。Indicates that the task finished transferring SQL ServerSQL Server Agent jobs.
TransferJobsTaskStartTransferringObjectsTransferJobsTaskStartTransferringObjects 指示任务已开始传输 SQL ServerSQL Server 代理作业。Indicates that the task started to transfer SQL ServerSQL Server Agent jobs.

传输登录名任务Transfer Logins Task

下表列出了传输登录名任务的自定义日志项。The following table lists the custom log entries for the Transfer Logins task.

日志项Log entry 描述Description
TransferLoginsTaskFinishedTransferringObjectsTransferLoginsTaskFinishedTransferringObjects 指示任务已传输完登录名。Indicates that the task finished transferring logins.
TransferLoginsTaskStartTransferringObjectsTransferLoginsTaskStartTransferringObjects 指示任务已开始传输登录名。Indicates that the task started to transfer logins.

传输主存储过程任务Transfer Master Stored Procedures Task

下表列出可传输主存储过程任务的自定义日志项。The following table lists the custom log entries for the Transfer Master Stored Procedures task.

日志项Log entry 描述Description
TransferStoredProceduresTaskFinishedTransferringObjectsTransferStoredProceduresTaskFinishedTransferringObjects 指示任务已传输完在 master 数据库中存储的用户定义的存储过程。Indicates that the task finished transferring user-defined stored procedures that are stored in the master database.
TransferStoredProceduresTaskStartTransferringObjectsTransferStoredProceduresTaskStartTransferringObjects 指示任务已开始传输 master 数据库中存储的用户定义的存储过程。Indicates that the task started to transfer user-defined stored procedures that are stored in the master database.

传输 SQL Server 对象任务Transfer SQL Server Objects Task

下表列出了传输 SQL ServerSQL Server 对象任务的自定义日志项。The following table lists the custom log entries for the Transfer SQL ServerSQL Server Objects task.

日志项Log entry 描述Description
TransferSqlServerObjectsTaskFinishedTransferringObjectsTransferSqlServerObjectsTaskFinishedTransferringObjects 指示任务已完成传输 SQL ServerSQL Server 数据库对象。Indicates that the task finished transferring SQL ServerSQL Server database objects.
TransferSqlServerObjectsTaskStartTransferringObjectsTransferSqlServerObjectsTaskStartTransferringObjects 指示任务已开始传输 SQL ServerSQL Server 数据库对象。Indicates that the task started to transfer SQL ServerSQL Server database objects.

Web 服务任务Web Services Task

下表列出了可以为 Web 服务任务启用的自定义日志项。The following table lists the custom log entries that you can enable for the Web Services task.

日志项Log entry 描述Description
WSTaskBeginWSTaskBegin 任务已开始访问 Web 服务。The task began to access a Web service.
WSTaskEndWSTaskEnd 任务已完成 Web 服务方法。The task completed a Web service method.
WSTaskInfoWSTaskInfo 有关任务的说明性信息。Descriptive information about the task.

WMI 数据读取器任务WMI Data Reader Task

下表列出了 WMI 数据读取器任务的自定义日志项。The following table lists the custom log entries for the WMI Data Reader task.

日志项Log entry 描述Description
WMIDataReaderGettingWMIDataWMIDataReaderGettingWMIData 指示任务已开始读取 WMI 数据。Indicates that the task began to read WMI data.
WMIDataReaderOperationWMIDataReaderOperation 报告任务所运行的 WQL 查询。Reports the WQL query that the task ran.

WMI 事件观察器任务WMI Event Watcher Task

下表列出了 WMI 事件观察器任务的自定义日志项。The following table lists the custom log entries for the WMI Event Watcher task.

日志项Log entry 描述Description
WMIEventWatcherEventOccurredWMIEventWatcherEventOccurred 表示发生了任务正在监视的事件。Denotes that the event occurred that the task was monitoring.
WMIEventWatcherTimedoutWMIEventWatcherTimedout 指示任务已超时。Indicates that the task timed out.
WMIEventWatcherWatchingForWMIEventsWMIEventWatcherWatchingForWMIEvents 指示任务已开始执行 WQL 查询。Indicates that the task began to execute the WQL query. 日志项包括查询。The entry includes the query.

XML 任务XML Task

下表介绍了 XML 任务的自定义日志项。The following table describes the custom log entry for the XML task.

日志项Log entry 描述Description
XMLOperationXMLOperation 提供任务所执行的操作的相关信息Provides information about the operation that the task performs

下面的列表包含一些链接,这些链接指向的主题说明如何执行与日志记录功能相关的任务。The following list contains links to topics that show how to perform tasks related to the logging feature.

用于完整和详细日志记录的 DTLoggedExec 工具(CodePlex 项目)DTLoggedExec Tool for Full and Detail Logging (CodePlex Project)