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