Using SSIS Logging to Resolve Runtime Errors and Performance Issues
October 11, 2010
Runtime errors and performance issues can be difficult to identify and resolve. One of the primary methods that assist with their resolution involves generating logs, which contain records of events taking place during code execution. This article provides a comprehensive overview of logging options available in SQL Server 2008 R2 Integration Services.
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 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.
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).