Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Integration Services (SSIS) Logging

Integration Services (SSIS) Logging

 

Applies To: SQL Server 2016 Preview

SQL Server Integration 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.

You can configure the scope of logging that occurs during a package execution on the Integration Services server. For more information, see Enable Logging for Package Execution on the SSIS Server.

You can also include logging when you run a package using the dtexec command prompt utility. For more information about the command prompt arguments that support logging, see dtexec Utility.

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. 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. The log provider specifies the format for the log data: for example, a SQL Server database or text file.

Integration Services includes the following log providers:

  • The Text File log provider, which writes log entries to ASCII text files in a comma-separated value (CSV) format. The default file name extension for this provider is .log.

  • The SQL Server Profiler log provider, which writes traces that you can view using SQL Server Profiler. The default file name extension for this provider is .trc.

    System_CAPS_noteNote

    You cannot use the SQL Server Profiler log provider in a package that is running in 64-bit mode.

  • The SQL Server log provider, which writes log entries to the sysssislog table in a SQL Server database.

  • The Windows Event log provider, which writes entries to the Application log in the Windows Event log on the local computer.

  • The XML File log provider, which writes log files to an XML file. The default file name extension for this provider is .xml.

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 SSIS Designer displays in the Configure SSIS Logs dialog box.

The following table lists the ProgID and ClassID for the log providers that Integration Services includes, and the location of the logs to which log providers write.

Log provider

ProgID

ClassID

Location

Text file

DTS.LogProviderTextFile

{0A039101-ACC1-4E06-943F-279948323883}

The File connection manager that the log provider uses specifies the path of the text file.

SQL Server Profiler

DTS.LogProviderSQLProfiler

{E93F6300-AE0C-4916-A7BF-A8D0CE12C77A}

The File connection manager that the log provider uses specifies the path of the file used by SQL Server Profiler.

SQL Server

DTS.LogProviderSQLServer

{94150B25-6AEB-4C0D-996D-D37D1C4FDEDA}

The OLE DB connection manager that the log provider uses specifies the SQL Server database that contains the sysssislog table with the log entries.

Windows Event Log

DTS.LogProviderEventLog

{071CC8EB-C343-4CFF-8D58-564B92FCA3CF}

The Application log in Windows Event Viewer contains the Integration Services log information.

XML File

DTS.LogProviderXMLFile

{440945A4-2A22-4F19-B577-EAF5FDDC5F7A}

The File connection manager that the log provider uses specifies the path of the XML file.

You can also create custom log providers. For more information, see Creating a Custom Log Provider.

The log providers in a package are members of the log providers collection of the package. When you create a package and implement logging by using SSIS Designer, you can see a list of the collection members in the Log Provider folders on the Package Explorer tab of SSIS 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. The SQL Server log provider uses an OLE DB connection manager. The Text File, SQL Server Profiler, and XML File log providers all use File connection managers. The Windows Event log provider does not use a connection manager, because it writes directly to the Windows Event log. For more information, see OLE DB Connection Manager and File Connection Manager.

To customize the logging of an event or custom message, Integration Services provides a schema of commonly logged information to include in log entries. The Integration 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. 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.

In SSIS Designer, you define the logging options by using the Configure SSIS Logs dialog box.

The following table describes the elements in the log schema.

Element

Description

Computer

The name of the computer on which the log event occurred.

Operator

The identity of the user who launched the package.

SourceName

The name of the container or task in which the log event occurred.

SourceID

The unique identifier of the package; the For Loop, Foreach Loop, or Sequence container; or the task in which the log event occurred.

ExecutionID

The GUID of the package execution instance.

System_CAPS_noteNote

Running a single package might create log entries with different values for the ExecutionID element. For example, when you run a package in SQL Server Data Tools, the validation phase might create log entries with an ExecutionID element that corresponds to SQL Server Data Tools. 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. These child packages might create log entries that have a different ExecutionID element than the log entries that the parent package creates.

MessageText

A message associated with the log entry.

DataBytes

A byte array specific to the log entry. The meaning of this field varies by log entry.

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

StartTime

The time at which the container or task starts to run.

EndTime

The time at which the container or task stops running.

DataCode

An optional integer value that typically contains a value from the DTSExecResult enumeration that indicates the result of running the container or task:

  • 0 - Success

  • 1 - Failure

  • 2 - Completed

  • 3 - Canceled

Integration Services supports log entries on predefined events and provides custom log entries for many Integration Services objects. The Configure SSIS Logs dialog box in SSIS 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

OnError

Writes a log entry when an error occurs.

OnExecStatusChanged

Writes a log entry when a task (not a container) is suspended or resumed during debugging.

OnInformation

Writes a log entry during the validation and execution of an executable to report information.

OnPostExecute

Writes a log entry immediately after the executable has finished running.

OnPostValidate

Writes a log entry when the validation of the executable finishes.

OnPreExecute

Writes a log entry immediately before the executable runs.

OnPreValidate

Writes a log entry when the validation of the executable starts.

OnProgress

Writes a log entry when measurable progress is made by the executable.

OnQueryCancel

Writes a log entry at any juncture in the task processing where it is feasible to cancel execution.

OnTaskFailed

Writes a log entry when a task fails.

OnVariableValueChanged

Writes a log entry when the value of a variable changes.

OnWarning

Writes a log entry when a warning occurs.

PipelineComponentTime

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

DiagnosticEx

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.

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.

Note that the DiagnosticEx event does not preserve whitespace in its XML output to reduce the size of the log. To improve readability, copy the log into an XML editor - in Visual Studio, for example - that supports XML formatting and syntax highlighting.

System_CAPS_noteNote

If you log the DiagnosticEx event with the SQL Server log provider, the output may be truncated. The message field of the SQL Server log provider is of type nvarchar(2048). 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. 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. For more information, see Custom Messages for Logging.

Log data includes the name and the GUID of the package to which the log entries belong. If you create a new package by copying an existing package, the name and the GUID of the existing package are also copied. 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.

To eliminate this ambiguity, you should update the name and the GUID of the new packages. In 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. You can also change the GUID and the name programmatically, or by using the dtutil command prompt. For more information, see Set Package Properties and dtutil Utility.

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. 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. To use the parent logging options, you set the LoggingMode property of the container to UseParentSetting. You can set this property in the Properties window of SQL Server Data Tools (SSDT) or through the Configure SSIS Logs dialog box in SSIS Designer.

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. The templates are stored in XML files.

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. Each log is associated with one of the following log providers: Text file, SQL Server Profiler, SQL Server, Windows Event Log, or XML file. 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.

You can set properties through SSIS Designer or programmatically.

A log provider is created and configured as a step in implementing logging in a package. For more information, see .

After you create a log provider, you can view and modify its properties in the Properties window of SQL Server Data Tools (SSDT).

For information about programmatically setting these properties, see the documentation for the LogProvider class.

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. For a list of these custom log entries and sample logging output, see Data Flow Task.

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.

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. Note that the DiagnosticEx event does not preserve whitespace in its XML output to reduce the size of the log. To improve readability, copy the log into an XML editor - in Visual Studio, for example - that supports XML formatting and syntax highlighting.

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 Services developers will recognize these steps as the principal methods of a PipelineComponent.

Step

Description

Validate

The component checks for valid property values and configuration settings.

PreExecute

The component performs one-time processing before it starts to process rows of data.

PostExecute

The component performs one-time processing after it has processed all rows of data.

ProcessInput

The transformation or destination component processes the incoming rows of data that an upstream source or transformation has passed to it.

PrimeOutput

The source or transformation component fills the buffers of data to be passed to a downstream transformation or destination component.

When you enable the PipelineComponentTime event, Integration Services logs one message for each processing step performed by each component. The following log entries show a subset of the messages that the Integration 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:

  • The OLE DB source that is named "Extract Data" spent 688 ms. loading data.

  • The Derived Column transformation that is named "Calculate LineItemTotalCost" spent 356 ms. performing calculations on incoming rows.

  • 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.

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN:


For automatic notification of these updates, subscribe to the RSS feeds available on the page.

DTLoggedExec Tool for Full and Detail Logging (CodePlex Project)

Community Additions

ADD
Show:
© 2015 Microsoft