Using SSIS Logging to Resolve Runtime Errors and Performance Issues

October 11, 2010

In general, software programming presents a unique set of challenges when it comes to troubleshooting coding mistakes. While problems surfacing during design stage and resulting from syntax or type conversion errors are relatively easy to detect and correct (this can be attributed to guidance incorporated into development tools, such as Business Intelligence Development Studio used to implement SQL Server 2008 Integration Services packages), runtime errors and performance issues are considerably more difficult to identify and resolve. One of primary methods that assist with their resolution involves generating logs, which contain records of events taking place during code execution. In this article, we will provide a comprehensive overview of logging options available in SQL Server 2008 R2 Integration Services.

An overwhelming majority of SSIS log entries represent events raised by packages and their components (you can find out more about them from our earlier article, "SSIS Events and Handlers"). Their creation is handled by log providers, which record arbitrarily chosen events in a designated target location, such as a file, a table hosted in a SQL Server database, a SQL Server Profiler trace, or the Windows Application Log. You have an option of either taking advantage of existing log providers or developing custom ones. We will focus here on the first of these two categories, which include the following provider types:

• SSIS log provider for Text files - produces output in the comma-separated values (CSV) format, stored in an arbitrarily chosen file (via a file connection manager), which can afterwards be easily imported into Excel for further analysis.
• SSIS log provider for XML files - yields an XML-formatted file (which implies its dependency on a file connection manager), making it suitable for a review with help of any XML-compatible viewer (and easily presentable in HTML-based reports).
• SSIS log provider for SQL Server - records event entries in the sysssislog table of msdb database (rather than sysdtslog90 used in SQL Server 2005 - although schemas in both cases are identical) by leveraging sp_ssis_addlogentry stored procedure. In order to be able to accomplish this, the provider requires an OLE DB connection manager.
• SSIS log provider for SQL Server Profiler - stores event data in *.trc files (which explains its reliance on a file connection manager) that employ SQL Profiler-specific format. This type of provider is intended primarily for troubleshooting performance issues, allowing you to correlate SQL Server-specific operations traditionally recorded in SQL Profiler traces with corresponding SSIS events.

SSIS log provider for Windows Event Log - dumps events in the Windows Application Log. For example, invoking package execution is recorded as Event ID 12556 and its completion is represented by the Event ID 12557 (both are easily identifiable by their SQLISPackage100 source)

It is worth noting that all providers that you assign to the package and its components share the same collection of events that are to be recorded along with specific data they should contain. As you might expect, at a minimum, you can choose from the set of events common to all SSIS components (which, as we described in more detail earlier, consist of On Error, OnInformation, OnWarning, OnPreValidate, OnPostValidate, OnPreExecute, OnPostExecute, OnExecStatusChanged, OnVariableValueChanged, OnProgress, OnQueryCancel, and OnTaskFailed). However, your selection is likely to be much wider, since it includes events specific to each component present in the package. In addition, you will also find an entry labeled Diagnostic, which is intended (based on enhancements introduced in Service Pack 2 of SQL Server 2008 Integration Services) for detailed troubleshooting of connectivity issues (it should be disabled otherwise due to the high volume of generated events, which are likely to have a negative performance impact). As indicated above, each log provider (with the exception of Windows Event Log Provider, which dumps its output directly to the Application Event Log) requires a corresponding connection manager defined as part of its configuration.

For each event type, you have the ability to specify the following individual pieces of data that should be logged.

• Computer (identifying the name of the system on which the recorded event took place)
• Operator (indicating the name of the user who invoked package execution)
• SourceName (providing the name of an executable, such as a task, container, or a package where the event originated)
• SourceID (Globally Unique Identifier - or simply GUID - matching ID property of the executable and assigned to it at its creation)
• ExecutionID (a unique identifier assigned to each package execution instance, allowing you to differentiate between two distinct runs)
• MessageText (providing a description associated with the event)
• DataBytes (containing log payload revealing auxiliary data about the event).

Each executable can be configured independently in regard to events and their details that should be recorded during its runtime. Effectively, you have the ability to log a particular event that takes place on the package level, but filter it out for each of its children components (and vice versa). In addition, it is possible to enforce different levels of detail to be recorded in each of these scenarios (excluding individual pieces of data if they are not relevant in a given context).

In order to better understand dependencies between different logging settings, let's review a sequence of steps necessary to configure them. Start by opening an SSIS package in the Designer interface of Business Intelligence Development Studio. Right-click on the empty area of its Control Flow tab and select the Logging... entry from its context sensitive menu (alternatively, you can choose the same item from the SSIS top-level menu). The resulting Configure SSIS Logs dialog box is divided in two sections. The left one, labeled Containers lists hierarchical structure of containers, with checkboxes next to each. The one on the right hand side is divided into two tabs - Providers and Logs and Details. The purpose of the first of them is to assign and configure a log provider that will be used to collect data during package execution. To accomplish this, highlight the top-level node in the Containers window (which represents the package), select one of five entries listed in the Provider type listbox (on the right) and click on the Add... command button. As the result, the selected provider will appear in the area labeled Select the logs to use for the container directly underneath. If your choice happened to be SSIS log provider for Windows Event Log, you can proceed to the next step. Otherwise, click on the listbox appearing in the Configuration column and either pick an existing connection or define a new one (via Configure OLE DB Connection Manager Editor in case of SSIS log provider for SQL Server or File Connection Manager Editor for all remaining types). Note that it is possible to create multiple log providers of the same type, which allows you to log events to multiple destinations.

With intended log providers and their corresponding connection managers in place, switch to the Details tab and select events to be logged. Turning on advanced view (by clicking on the Advanced>> command button), will additionally allow you to specify pieces of data you are interested in (such as Computer, Operator, SourceName, SourceID, ExecutionID, MessageText, and DataBytes). In essence, this gives you the ability to choose specific events along with individual data for each by marking on or off checkboxes in a table-like structure (where rows represent event types and individual pieces of data appear as columns).

Logging on the package level can be either enabled or disabled, however child containers have three possible settings. The third one, represented by the grayed checkbox, indicates that logging configuration is inherited from the parent (you also have the ability to determine the resulting value for a given container by checking whether its LoggingMode property is set to Enabled, Disabled, or UseParentSetting). This is convenient if you want to enforce consistency across all executables (since it precludes changing them in all children).

Once filtering settings are configured, you have an option to evaluate whether they yield the desired level of logging without generating (and reviewing) actual logs. Instead, prior to invoking package execution, activate the Log Events window (accessible via View->Other Windows menu) in the Designer interface of Business Intelligence Development Studio (to accomplish the same when invoking package execution via the DTExec command line utility, take advantage of its /ConsoleLog switch). Content of the window will reflect assigned settings even if log providers have been temporarily disabled.

If you want to apply the same configuration to multiple packages, use the Save... command button in the Configure SSIS Logs dialog box (while the top node is highlighted in its Containers section). This will allow you to store current logging settings in an XML formatted file, which you can subsequently apply to another package loaded into Business Intelligence Development Studio (via the Load... command button in the same dialog box).

» See All Articles by Columnist Marcin Policht