Importing IIS Web Log into SQL Server 2005

Today I was trying to import the IIS log of my Web Server into SQL Server 2005 Database (for further analyses). So first I started googling about it and found this link,

How To Use SQL Server to Analyze Web Logs

Very useful article BUT this one is old maybe because the table have different schema. By the way I have IIS 6.0 on my web server. So I did some workarounds and successfully imported the Log. Now I am posting here in case someone might be trying to do the same thing.

Step #1: Create a table in Database

CREATE TABLE [dbo].[IISLog](
 [date] [datetime] NULL,
 [time] [datetime] NULL,
 [site-name] [varchar](255) NULL,
 [s-computername] [varchar](255) NULL,
 [s-ip] [varchar](50) NULL,
 [cs-uri-stem] [varchar](255) NULL,
 [cs-uri-query] [varchar](2048) NULL,
 [c-ip] [varchar](50) NULL,
 [cs(User-Agent)] [varchar](2048) NULL,
 [cs(Cookie)] [varchar](2048) NULL,
 [cs(Referer)] [varchar](2048) NULL,
 [sc-status] [int] NULL,
 [sc-bytes] [int] NULL,
 [cs-bytes] [int] NULL,
 [time-taken] [int] NULL
)

Step #2: Prepare the Log file (since it contains some description lines on top)

So this is a tricky step. As the Log file contains some description lines on top starting with "#" sign. Therefore SQL Server will NOT be able to import it. One more thing is that these log files can be large (or very large). The Log file I had was of size aprox. 216 MB. So obviously we cannot open it in NotePad etc.

The same article provides a small utility which removes the line, but I think there is bug in this utility, cause it is limiting file upto 43 MB. So I decided to write my own version.

PrepIISLogFileForImport.zip

As I started to create this application for IIS Log import but then it ends up with a generic utility. Which actually displays the text file content and have an option to skip number of lines from start. Therefore we can use it as,

C:\>PrepIISLogFileForImport C:\LogFile.Log skip=4 >newlogfile.txt

Step #3: Bulk import the Log file into SQL Server Table

Hence the final step is to import the log file in the same table. Which can be done by,

BULK INSERT [dbo].[IISLog] FROM 'C:\newlogfile.txt'
WITH (
    FIELDTERMINATOR = ' ',
    ROWTERMINATOR = '\n'
)