import from text file to SQL Server Database, what solution I should choose?

Question

I have to import a log file to SQL Server Database. File size is big, around 6MB with 40000 lines. The task of importing has to be done everyday with daily log. Each line of the log file contains many fields, which I have to import into properly columns in Database for post-processing. I'm confused of these solutions:

-Use SQL Server Integration Services to do this.

-Write a C# app using BULK COPY

I'm relatively free to choose solution (in SQL Server and .NET framework). What solution is better for this, or you can suggest another one.

Thank you very much.

//Edit: I tried SSIS and saw that it's really simple. But everyday, after receiving log file, my program has to automatically import it into database. How can I do that?

Answer 1

I would probably write a script that converts the log file into an sql dump that insert the log file strings and then load that sql dump into the database.

Answer 2

6MB is actually pretty small :)

SQL Server Integration Services is more than up to the task. BULK COPY can become complicated really quickly, especially for people who are new to this. As a 3rd option, you could write your own program to do INSERTS, but then again, that's what SSIS was built for, so just stick with that.

Answer 3

I would write an SSIS package to do this.

You could use the import/export wizard to generate the beginings of a package and adapt it to meet your exact needs.

To do this in SQL 2005, Right click on your database in object explorer in SQL Management Studio, go Tasks > Import Data, follow the wizard and at the end select to save the package.

I imagine it's a similar process in SQL 2008, but I don't have it to hand.

After you have saved your package it's possible to schedule it using the SQL Server aAgent, when setting up the job, choose "SQL Server Integraton Services Package" as the type and select your packege.