Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 11, 2005

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

By Marcin Policht

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM