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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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
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
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dan McFaddenSystems EngineerCommented:
Any luck on this?

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

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