Hello Guys & Girls,
You'll have to excuse me on this one as i'm not muh of an SQL guy, my background is in ColdFusion development, so whilst i have a reasonable understanding of basic query language, i'm a little lost as to where i should start with task.
Basicly I have an application in mind for working with IIS Server logs, which as you may know come in a space delimited file, and can grow to be reasonably large. It's been reccomended to me by serveral people that the most efficient way to import such a large amount of data is using a SSIS, however i have NO SSIS experience what so ever, and dont even know where to begin, so thought i'd come here for a little assistance.
Now, the log file can consist of several thousand lines, each containing 20 delimited pieces of information that need to be placed into the database.
The document also includes comment lines started with a pound sign, somthing like this.
# This is a comment.
Which need to be ignored as they are redundant data. So as an example the file looks somthing like this.
Now, I have all the code I need to fire the DTS from a stored proc on the server side, I just need help writing a DTS that will import this into my database table. I'll need to pass an argument in, which contains the location of the file to be imported as this changes on a daily basis.
Thanks for any help you can give guys, just as a note I'm running on SQL2005.
I played around a little this morning with this, setting my header lines a # and asking SSIS to skip the first 4, then use the top line as the column names, and this almost works, however it seems to recognise the first 4 columns fine, but after that it seems to merge them all into a single column, despect me being able to see a ' ' as the delimiter in the file.
The other element to take into configuration is that the IIS logs change on a daily basis, so setting a static file name might be a bit of a pain, is there any way i can make the file path dynamic, and i can then pass the path in as a variable?
Thanks guys,
Rob
Rob
You'll have to excuse me on this one as i'm not muh of an SQL guy, my background is in ColdFusion development, so whilst i have a reasonable understanding of basic query language, i'm a little lost as to where i should start with task.
Basicly I have an application in mind for working with IIS Server logs, which as you may know come in a space delimited file, and can grow to be reasonably large. It's been reccomended to me by serveral people that the most efficient way to import such a large amount of data is using a SSIS, however i have NO SSIS experience what so ever, and dont even know where to begin, so thought i'd come here for a little assistance.
Now, the log file can consist of several thousand lines, each containing 20 delimited pieces of information that need to be placed into the database.
The document also includes comment lines started with a pound sign, somthing like this.
# This is a comment.
Which need to be ignored as they are redundant data. So as an example the file looks somthing like this.
Code:
#Software: Microsoft Internet Information Services 6.0
#Version: 1.0
#Date: 2007-02-16 04:04:41
#Fields: date time s-sitename s-computername s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs-version cs(User-Agent) cs(Cookie) cs(Referer) cs-host sc-status sc-substatus sc-win32-status sc-bytes cs-bytes time-taken
2007-02-16 04:04:41 W3SVC556634385 UKFAST-HBWGVXAU 192.168.32.134 GET /robots.txt - 80 - 74.6.85.149 HTTP/1.0 Mozilla/5.0+(compatible;+Yahoo!+Slurp;+[URL unfurl="true"]http://help.yahoo.com/help/us/ysearch/slurp)[/URL] - - 80.244.184.134 404 0 0 1814 191 93
2007-02-16 04:07:48 W3SVC556634385 UKFAST-HBWGVXAU 192.168.32.134 GET / - 80 - 74.6.86.125 HTTP/1.0 Mozilla/5.0+(compatible;+Yahoo!+Slurp;+[URL unfurl="true"]http://help.yahoo.com/help/us/ysearch/slurp)[/URL] - - 80.244.184.134 200 0 0 772 231 109Thanks for any help you can give guys, just as a note I'm running on SQL2005.
I played around a little this morning with this, setting my header lines a # and asking SSIS to skip the first 4, then use the top line as the column names, and this almost works, however it seems to recognise the first 4 columns fine, but after that it seems to merge them all into a single column, despect me being able to see a ' ' as the delimiter in the file.
The other element to take into configuration is that the IIS logs change on a daily basis, so setting a static file name might be a bit of a pain, is there any way i can make the file path dynamic, and i can then pass the path in as a variable?
Thanks guys,
Rob
Rob
