It&#39;s Log Parser Day! Robert Sheldon shows how Log Parser can be used for ETL

SQL Home - SQL Tools - Microsoft's Log Parser Utility: Swell ETL

For loading text, CSV or XML files into SQL Server, the Log Parser utility, with its amazing SQL engine, is likely to be the obvious choice. Although initially developed purely for converting IIS logs, the Log Parser can turn its hand to a range of formats including even event logs or the windows registry.

First off, Microsoft’s Log Parser utility is not a SQL Server tool. Log Parser is a powerful Windows command-line utility that can extract data from a variety of sources—IIS logs, XML and CSV files, Active Directory objects, Network Monitor capture files, and the Windows registry, to name a few—and output the data to various files and systems, most notably SQL Server. In fact, Log Parser makes importing data into a SQL Server database so simple, you’ll wonder why you haven’t been using the tool all along.

At the core of the Log Parser utility is a “SQL-like” engine that processes data as it’s retrieved from the source and sent to the destination. You can think of Log Parser as a mini extract, transform, and load (ETL) application that uses input formats to extract data from its source and output formats to send the data to its destination.

An input format provides the source data to the engine as a record set, similar to the way rows are stored in a table. Each input format serves as a record provider specific to the source from which the data is retrieved. For example, you would use the xml input provider to retrieve data from an XML file.

Output formats also present the processed information as record data, with each output format specific to the target destination type. If you were sending data to a SQL Server database, for instance, you would use the sql output format.

For details about the available input and output formats supported by Log Parser, as well as information about other features, see the Log Parser help file (LogParser.chm). The file is added to the directory where Log Parser is installed when you do a complete installation or you include the documentation component as part of a custom installation. You can download Log Parser from the Microsoft Download Center. There you will also find installation instructions.

Using Log Parser to retrieve data

Log Parser comes in two versions: a command-line executable and a DLL containing COM objects that applications can use to run Log Parser operations. This article focuses on the command-line utility and how you can use it to import data into a SQL Server database.

...

Working with Log Parser

The examples I’ve shown you in this article have all retrieved data from the System event log, but you’re certainly not limited to that log. You can retrieve data from other event logs, multiple logs, and a variety of other sources, such as Active Directory, the registry, IIS logs, text files, or information about the file directory itself. Log Parser is a flexible and powerful tool that can be useful in a variety of circumstances. And because of the utility’s “SQL-like” logic, most of the data you can retrieve through Log Parser can be saved to a SQL Server database. If you can write a T-SQL SELECT statement, you can use Log Parser to store all sorts of information in your SQL Server databases.