The Stock Log Providers

As with other SSIS pluggable components, Microsoft provides some stock log providers that cover a broad range of logging requirements. There are no dedicated designers for log providers like there are for tasks and components, but the designer does provide a way to edit the filter and output settings generically. Because each log provider supports a different output format, each requires a slightly different configuration. Following is a description of each log provider and how to configure it.

Windows Event Log Provider

The Windows Event Log Provider writes logs to the Windows event log and so it doesn't require a connection manager at all. This has been confusing to some users who attempt to create a connection manager or otherwise enter some value for the configuration in the Logging Configuration dialog box. For this log provider, it is safe to ignore the configuration.

Tip

To set up a log provider for a package you run with DTExec, you use the /Logger switch. Normally, the /Logger switch expects two parameters within quotes: the name of the log provider to use and the configstring argument within the quotes. Because the event log doesn't require a configuration, you would think the command line would look similar to this:

DTExec ... /LOGGER "DTS.LogProviderEventLog.1" 

However, DTExec looks for the semicolon to determine the end of the log provider name. So, to successfully configure the Windows Log Provider using DTExec, use the following format with the semicolon appended to the name inside the quotes and a dummy configuration.

DTExec ... /LOGGER "DTS.LogProviderEventLog.1;Foo" 

Text File Log Provider

The Text File Log Provider writes out log entries to comma separated values in text files. The files generated with the Text File Log Provider are amenable to viewing in Excel or consumed in the Data Flow Task, which makes it simple to have a postprocessing Data Flow Task that does analysis on the logs. The LoadTxtLog.dtsx sample package in the S11-Logging solution shows how to set up the Data Flow Task to consume a text log file. There is a little trick to getting the header just right because of the "#Fields:" string the text provider inserts. This package consumes the text log file that the Logging.dtsx sample package generates, so be sure to run the Logging.dtsx sample package before running the LoadTxtLog.dtsx sample package.

Tip

The trick is as follows: In the Flat File Connection Manager, specify the string "#Fields:" as the header row delimiter, set the header rows to skip to 1, and then use the first row of data as the header by putting a check mark in the Column Names in the First Data Row check box.

XML Log Provider

You configure the XML Log Provider the same as you do the Text File Log Provider. The difference is, of course, that the XML Log Provider creates an XML file. Figure 11.5 shows the XML log file generated from the sample package.

Figure 11.5. View XML logs in any browser

The advantage to the XML format is that the logs can be consumed by any XML-aware system without concern for compatibility with the Integration Services logging schema. You can also use XML utilities such as XSL transforms to convert logs to HTML reports.

SQL Server Log Provider

The SQL Server Log Provider adds log entries to the system table called sysdtslog90 and requires an OLEDB connection to an instance of SQL Server and containing the target database name. If the sysdtslog90 table does not exist in the destination database, the SQL Server Log Provider creates it. The default database is MSDB.

The advantage of this logging method is that you can use the full querying power of SQL Server to process the logs. It's a good choice as your default log provider.

SQL Profiler Log Provider

As you saw previously, the SQL Profiler Log Provider requires a file connection manager and outputs the log entries in native SQL Profiler format. Logging to SQL Profiler makes it possible to correlate the log entries from the package to other activities that you want to view in SQL Profiler, such as queries, stored procedures, and so forth while helping you analyze the impact package runs have on system performance.

Designer Log Events ViewerThe "Virtual Log Provider"

Occasionally, it's nice to be able to see the logs in the designer as you are developing packages. The Designer Log Events window isn't a log provider, but it functions in much the same way as log providers do. It only displays log entries according to the filter settings in the Logging Configuration dialog box. It is something like a virtual log provider as the package writes none of the log entries to disk anywhere. Figure 11.6 shows the Log Events window after executing the Logging.dtsx sample package.

Figure 11.6. The Log Events window shows log events inside the designer

The advantage of this logging method is that you don't actually have to configure a log file or SQL connection. Simply enabling logging on containers is sufficient. As the package matures and you better understand how the logging requirements apply to the package, you can configure one of the other log providers as appropriate. This window can be used to quickly determine which combination of filters to use to get just the right logging coverage.

Tip

DTExec.exe also provides a similar function using the /consolelog parameter.