SQL Server 2005 - SQL Server Integration Services - Part 12 - Logging
November 11, 2005
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:
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:
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.