Importing IIS logs into MS SQL

There are two solid approaches for getting IIS log data into SQL Server for analysis.

Method 1: Native SQL with BULK INSERT

Create the target table and bulk import a log file directly:

DROP TABLE IF EXISTS dbo.IISLOG
CREATE TABLE dbo.IISLOG (
  [DATE] [DATE] NULL,
  [TIME] [TIME] NULL,
  [s-ip] [VARCHAR] (48) NULL,
  [cs-method] [VARCHAR] (8) NULL, 
  [cs-uri-stem] [VARCHAR] (255) NULL,
  [cs-uri-query] [VARCHAR] (2048) NULL,
  [s-port] [VARCHAR] (4) NULL,
  [s-username] [VARCHAR] (256) NULL,
  [c-ip] [VARCHAR] (48) NULL,
  [cs(User-Agent)] [VARCHAR] (1024) NULL,
  [cs(Referer)] [VARCHAR] (4096) NULL, 
  [sc-STATUS] [INT] NULL,
  [sc-substatus] [INT] NULL,
  [sc-win32-STATUS] [BIGINT] NULL,
  [time-taken] [INT] NULL,
  INDEX cci CLUSTERED COLUMNSTORE
)

BULK INSERT dbo.IISLog
FROM 'c:\tmp\IisLogs\u_exyymmdd.log'
WITH (
  FIRSTROW = 2,
  FIELDTERMINATOR = ' ',
  ROWTERMINATOR = '\n'
)

Replace the filename with the actual IIS log file you want to import. IIS log files typically start with comment lines, so FIRSTROW = 2 skips the first header row (you may need to adjust this based on how many comment lines your logs have).

Method 2: Microsoft Log Parser

For large datasets spanning days or months, Microsoft Log Parser is far more efficient. It handles wildcard patterns, eliminating the need for loop scripting:

PS C:\Program Files (x86)\Log Parser 2.2> .\logparser.exe "SELECT * INTO iisLogs FROM c:\tmp\IisLogs\u_ex*.log" -i:iisw3c -o:SQL -server:localhost\SQLExpress2017 -database:IisLogs -createTable:On

The u_ex*.log wildcard pattern will match all IIS log files in the directory, making it easy to import an entire month’s worth of logs in one command. The -createTable:On flag creates the destination table automatically if it doesn’t exist.