Share via

Implementing Logging in Packages

  • Article

SQL Server 2005 Integration Services (SSIS) includes logging features that write log entries when run-time events occur and can also write custom messages.

Integration Services supports a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files.

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.

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.

Log Schema

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.

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 do not appear 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

Log Entries

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 the execution status of the executable changes.

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.

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.

Differentiating Package Copies

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 Business Intelligence Development Studio, 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 Setting Package Properties and dtutil Utility.

Parent Logging Options

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 Business Intelligence Development Studio or through the Configure SSIS Logs dialog box in SSIS Designer.

Logging Templates

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 How to: Enable Logging in a Package.

  3. Select the events and the log schema information about each event you want to capture in the log. For more information, see How to: Configure Logging in a Container.

To view log entries during package development

See Also

Concepts

Integration Services Log Providers

Other Resources

Creating Packages in SSIS Designer

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

15 September 2007
New content:
  • Added table that describes the elements in the log schema that do not appear on the Details tab.

5 December 2005
New content:
  • Added information about log schemas and custom messages for log entries.
  • Explained how to differentiate package copies.
Changed content:
  • Expanded description of package-level logging vs. logging on individual objects.