SQL Server 2005 – SQL Server Integration Services – Part 12 – Logging

In one of our earlier articles dedicated to the WMI Event Watcher Task of
the SQL Server 2005 Integration Services, we briefly mentioned SSIS logging
functionality (which needs to be enabled in order to configure the ActionatEvent
configuration WMI option). This allows us to verify the outcome of execution by
checking the content of the log file generated as the result of Windows
Management Instrumentation event. As we pointed out, the same could be done in
order to record SSIS events, which we discussed in the previous article of this
series. At the same time, SSIS logging also provides a convenient alternative
to monitoring package execution by displaying relevant information via message
boxes generated through an appropriately formulated Script Tasks code – which
is the approach we have been using so far in the majority of our examples. In
this article, we will take a closer look into the various characteristics of SSIS
logging and their corresponding configuration options.

The Logging feature provides the capability of capturing SSIS events (for
more information on this subject, refer to our previous
article
). As with Event Handlers, categories of logged entries correspond
to event types that they record, for example, OnError, OnExecStatusChanged, OnInformation,
OnPostExecute, OnPostValidate, OnPreExecute, OnPreValidate, OnProgress, OnQueryCancel,
OnTaskFailed, OnVariableValueChanged, or OnWarning (also described in more
detail in the same article). As mentioned earlier, there are also events, which
are component specific (for example, OnWMIEventWatcherEventOccurred and OnWMIEventWatcherEventTimedout,
associated with the WMI Event Watcher task). For each of them (regardless of
category), you can gather individual pieces of information, which are defined
as elements of the SSIS log schema and include:

  • Computer – containing the name of the system on which the logged
    event took place,

  • Operator – indicating the account name of the user who launched
    the package execution,

  • SourceName – identifying task or container where the event
    originated,

  • SourceID – providing a unique ID of the SourceName executable.
    This takes the form of the 16-byte Globally Unique Identifier (GUID), generated
    automatically when each component is created and stored as its ID property.
    Note that the package ID (and ID of its respective components) might become
    duplicated if a package is copied to the file system. In case this constitutes a
    problem (since it makes it difficult to differentiate between information
    logged by the original and its copy – especially if both point to the same log
    file), you can generate a new ID, following the process outlined in the Microsoft Knowledge Base
    article KB906564
    .

  • ExecutionID – specifying a unique ID of the package execution
    instance – in the same, 16-byte GUID format. This allows you to distinguish
    between multiple runs, especially in cases where information needs to be
    extracted and correlated from several log files.

  • MessageText – describes event characteristics in more detailed
    fashion.

  • DataBytes – formed as the byte array, it provides the ability to
    include additional, event specific information (it is reserved for future use).

In addition, by default, logs also include start and end date and the time
of event’s execution as well as the Data Code, identifying the event. Note that
it is possible to record only the schema elements that you are interested in,
in order to minimize disk space utilization.

Similar granularity is available when configuring individual package
components. Logging can be enabled separately for each of the executables, such
as tasks and containers, as well as the package itself (which is a special type
of container) – which, as you might expect, are components capable of
generating and detecting events. Depending on your preferences, logging options
are either shared or set independently, varying in the range of event types to
be tracked or the level of details to record for each event type. The same
applies to the format and destination of logs, which are determined by logging
providers, assigned to each log. Logging providers are software components,
which determine the destination where logging data is stored as well as its
format, and include the following:

  • SSIS log provider for Text files – the most straightforward
    option, redirecting entries to a comma-separated value (CSV) text file. In
    order to review them, you might want to consider importing the content of the
    file into an Excel (or other application capable of dealing with structured
    text files).

  • SSIS log provider for SQL Profiler – stores logged entries in the
    format (as a .TRC file) that can be viewed in the SQL Server 2005 Profiler.
    This gives you the ability to correlate entries generated in response to
    monitored events with other types of data available via Profiler (such as
    stored procedure activities, performance or locking-related traces, etc.)

  • SSIS log provider for SQL Server – loads event entries into a SQL
    Server database table called sysdtslog90 (with such columns as event, computer,
    operator, source, sourceid, executionid, starttime, endtime, datacode, databytes,
    and message, which match the elements of SSIS log schema), using the OLE DB
    connection settings (this connection gets associated with the provider at the
    time of its creation). This gives you the ability to extract logging
    information via T-SQL queries (for example, using Execute SQL Task).

  • SSIS log provider for Windows Event Log – records event
    information in the Windows Application Log, which you can monitor (and
    configure to send alerts and take appropriate corrective actions) with a
    systems management software (such as Microsoft Operations Manager or any
    similar third party program capable of extracting this information). The
    entries point to SQLISPackage as their source and, depending on the content,
    might be labeled as Informational, Warning, or Error messages. In case the
    format and content of the event information created in this manner is not
    sufficient, you also have the option of redirecting SSIS log entries to the
    Windows Application Event log through a code invoked via a Script Task. Details
    of this approach, including the relevant code, are documented in MS Knowledge
    Base article KB906560
    .

  • SSIS log provider for XML files – dumps event entries into an
    XML-formatted file, which can be either viewed via an XML viewer of your
    choice, or displayed through a Web page. This approach also facilitates transferring
    relevant information to a third party system, which is not capable of working
    properly with the output from other logging providers.

Similar to the way inheritance of Event Handlers can be configured
throughout task and container hierarchy, it is also possible to apply logging
settings assigned to a parent container to all of its executable components.
This is done by setting their LoggingMode property to UseParentSettings value.
This property can be also explicitly enabled or disabled – using two remaining
configuration values (by default, the value of LoggingMode property is set to
Enable).

Now let’s take a look at the actual process that allows us to implement the
options described above. For the purpose of this exercise, you can use the same
package we created in order to demonstrate features of the WMI Event Watcher
Task (this will simplify testing OnError event, triggered when a monitored WMI
event does not take place before the Timeout period expires – which is the same
approach we applied in the previous article). Once you have accessed the
Control Flow area of the designer interface in the SQL Server 2005 Business
Intelligence Development Studio, right click on it and select the Logging…
entry from the context sensitive menu. This will present you with the Configure
SSIS Logs:Package dialog box, with the list of containers on the left hand side
and Providers and Logs tab (as well as Details tab visible but not active) to
its right. By marking or unmarking checkboxes located next to each executable
displayed in the Containers area, you control the value of their LoggingMode
property. As expected, marked checkboxes correspond to LoggingMode set to
Enabled and clearing it sets LoggingMode to Disabled. The third setting – with
marked on checkbox and grayed out background indicates the value of UseParentSettings.

As long as the value of LoggingMode is Enabled for a particular component,
the entries on the Providers and Logs tab for it are configurable (otherwise,
as you can expect, they are either disabled or inherited from its parent
container). However, you keep in mind that while (as we explained before) it is
possible to assign different destinations for logs generated by each of the
components, all of them need to be created on the Package level first. This is
done by selecting the Package entry in the Containers area (on the left hand
side of the Configure SSIS Logs:Package dialog box) and clicking on the Add
command button on the Providers and Logs tab to add a new log with the appropriate
entry selected in the Provider type list box. Once this is completed, you
simply enable or disable the ones you intend to use for individual components
(as long as you have set their LoggingMode property to Enabled), by marking on
the checkbox next to the relevant entry on the Logging providers list (on the
Providers and Logs tab).

For each logging provider entry you add, you need to specify the appropriate
Configuration entry, corresponding to the matching Connection Manager. This is a
File Connection (configurable via File Connection Manager Editor dialog box)
for SSIS log provider for Text files, SSIS log provider for SQL Profiler, and
SSIS log provider for XML files (depending on the provider, the format of the
file content will differ accordingly) or OLE DB Connection (which you set using
Configure OLE DB Connection Manager) for SSIS log provider for SQL Server. SSIS
log provider for Windows Event Log does not require any additional
configuration, since its purpose is to redirect the information about events to
the Windows Application Event log. (Note, however, that if you are using SQL
Server 2005 June Community Technology Preview, you might receive a nonfatal
error notification about the connection "" not being found when saving
the package with the SSIS log provider for Windows Event Log – but despite this
oddity, you should still be able to use its functionality). While, as we
mentioned before, creating new logging provider entries is possible only on the
Package level, assigning connection managers to each of the existing entries
can be done on the component level (as long as their LoggignMode property is
Enabled). Keep in mind though that each such change applies to all other
components using the same entry.

The Details tab on the right hand side of the Configure SSIS Logs:Package
dialog box allows further customization of logging settings. Options available
there allow you to specify which types of events should be monitored for the
container currently selected in the Containers list (on the left hand side of
the dialog box). By clicking on the Advanced command button underneath, you
will be able to select elements of the SSIS log schema (Computer, Operator, SourceName,
SourceID, ExecutionID, MessageText, and DataBytes) to be recorded for each
event type.

The logging configuration options that you specified within the Configure
SSIS Logs:Package dialog box can be saved using the Save… command button
available from the Details tab. This creates an XML formatted file, which subsequently
can be loaded (using the Load… command button from the Details tab) in case
you want to apply identical logging settings across multiple packages.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles