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

Just make the column 'text' and you won't run into any length restrictions. Once you're done importing, scan the column for values > 8000 characters and see what that is about.

Sometimes a line in the IIS file doesn't write properly and you get missing columns or overlapping columns and that could be causing an issue. But using 'text' will certainly eliminate the data truncation issue.

After the import you can always setup a new column called [cs(User-Agent)varchar] that is varchar(8000) and copy the data into it using convert(varchar(8000),[cs(User-Agent)])

Answer 2

Im not sure if this is the right way.

but this solved my problem

In choose a data source step. i selected advanced and specified output column width as 5000 and it worked

Answer 3

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...