Import IIS log into SQL Server 2008 error

Question

I'm trying to import IIS logs into SQL Server 2008. I get this error below.

Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "cs(User-Agent)" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)

I tried changing the column width of user agent to varchar(8000) and nvarchar(4000) no luck. pls help

-Vivek

Answer 1

How are you performing the import?

I'd also look at Microsoft's LogParser (as Mayo mentioned in a comment), as well as querying the files in a SQL-like manner, you can also use it to import the files into a SQL Server database, and it will take care of creating the target tables for you. Syntax is something like (replacing the {...} placeholders as appropriate):

LOGPARSER "SELECT * INTO {outtable} FROM {all090623.log}" 
        -o:SQL -server:{myserver}
        -database:{weblogs}
        -driver:"SQL Server" 
        -createTable

Once it's in the database you can add indexes as usual, and away you go...