Distribution of data in sql server

Hello,

I have some ideas to process the following steps but will appreciate it if some experts share their thoughts with me:
(frequency is daily)

1) Information from IIS log file will be exported into a CSV (using IIS Logger in PS) file then a C# code will insert them all in a table (let's call it RawTable).

2) After having all the information for let's say for today, I need to break them into different tables. One table holds onto Client IPs and Server IPs, one table will have the browsers info and cookies information and etc

In general I'm having around 7 tables.  

what technique do you suggest I go with? should I write few stored procedures or a 3rd party tool?''

*This process happens every midnight or early morning.


Thanks.
akohanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Definatily go for Stored Procedure. That gives you all the flexibility that a 3'rd party tool wont give you.

If you need "regular expressions" to mine your data, this can be incorporated into the SQL instance using this technique:
https://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/

Regards Marten
0
Dan McFaddenSystems EngineerCommented:
Why do must you do an export to CSV.  IIS's HTTP logs are well documented and well formed.  Why not just have the C# code consume the original logs?  This way you remove an un-needed step in the loading process.

Why not take it one step further?  instead of importing the https logs file, line for line, into a table... why not process the logs using C#?  Then store the parsed data into your 7+ tables.  This removes a second step in the loading process.

To do this in a different way...

1. consume http logs with Microsoft's Lop Parser tool (http://technet.microsoft.com/en-us/scriptcenter/dd919274.aspx)
2.  run log parser with the various SQL-like queries to get the data you want into your tables.
3. output the results of your queries to a csv file
4. use the SQL Server Bulk Insert command to put the data into your 7+ tables.  http://msdn.microsoft.com/en-us/library/ms178129(v=sql.110).aspx

I guess it all depends on how much work you want to do or if this is just a learning experience.

I'd say, to learn.. go the C# coding path.  To get it done with tools that are readily available and well documented... Log Parser and Bulk Insert.

Dan
0
akohanAuthor Commented:
Hi Dan,

Yes, already I am using Log Parser but didn't think of Bulk Insert. I heard it in past but never used it.

I will get back to you guys,
Thanks.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dan McFaddenSystems EngineerCommented:
Any luck on this?

Dan
0
akohanAuthor Commented:
Hello Dan,

Yes, I'm thinking of Sql Server Bulk insert but can it be automated?

The truth is that I'm using LogParser so currently *.log > *.csv >  then my code reads it and inserts into database but now that you have pointed out to Sql Server Bulk insert then thinking of using it.

Thanks.
0
Dan McFaddenSystems EngineerCommented:
Yes, it can be automated.  Below are links for reference and examples:

Background:  http://msdn.microsoft.com/en-us/library/ms141239(v=sql.110).aspx

Developer Docs:  http://msdn.microsoft.com/en-us/library/ms188365.aspx

Please note, I didn't notice a reference to what version SQL Server you're using, but you can use the drop-down near the center-top of the links to choose your version.  I used SQL Server 2012 in the links.

Dan
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
akohanAuthor Commented:
Thank you for the details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.