Web logs

[pto]

Hello,

I have been assigned a task to take log files from the web server that have been converted using a program called
preplog.exe that Microsoft gives gives when you are trying to analyize the log files. It apparently converts the logs into a text file that you should be able to bulk insert
into a database that you create on a sql server. I was
able to create the database no problem, it even gives you
a script to create the tables. However, in looking at the
file the fields are different from the script they give you to generate the fields. I then looked at the raw text and the database fields and changed them to match. However, when I perform the bulk insert I get an error that it cannot locate the file. The following is the script that was given to me.

CREATE TABLE [dbo].[tablename] (
[date] [datetime] NULL,
[time] [datetime] NULL ,
[c-ip] [varchar] (50) NULL ,
[cs-method] [varchar] (50) NULL ,
[cs-uri-stem] [varchar] (255) NULL ,
[cs-uri-query] [varchar] (2048) NULL ,
[sc-status] [int] NULL ,
[sc-bytes] [int] NULL ,
[time-taken] [int] NULL ,
[cs(User-Agent)] [varchar] (255) NULL ,
[cs(Cookie)] [varchar] (2048) NULL ,
[cs(Referer)] [varchar] (2048) NULL
)

Any assistance would be greatly appreciated.

 

[JayKusch]

try using a DTS import instead
Thanks

J. Kusch

 

[pto]

Thanks but, does not dts import require another server. The web files are converted and are burned onto cd.

 

[JayKusch]

No sir ... they can reside on any accessible drive. have done the same type of "web log" loading into SQL Server but from Apache logs.
Thanks

J. Kusch

 

[pto]

Did you use the wizard to create the dts? Would you please give an example.

Thanks for the help.

 

[JayKusch]

Morning'

here is a brief rundown of what you may do to get the DTS package built.

1. goto DTS:Local Packages in Enterprise Manager
2. r-click on Local Packages and choose 'New Package'

-- Set up the import file from the CD

3. from the Connection group choose: "Text File (Source)" and drag it into the work area
4. it will bring up the Connection Properties
- Set the file name to the CD drive and/or directory that the burned file resides on. Click Next ...
- check the properties to match the type of data you plan to pump in. Click Next ...
- Set the column delimiter. Click Finish and the OK

-- Now set up the table you plan to import/pump data into

5. from the Connection group choose: "Microsoft OLE DB Provider for SQL Server" and drag it into the work area

6. once again, set you Connection Properties for the Server we are planning to pump into.
- Set the server, either Local or another named Remote
- Set the user/pass if need to log onto the server
- Set the DB that the table resides
- Click OK

-- We are now set to create the conduit to push the text file to the SQL Server table

7. from the Task area choose "Transform Data Task" then click on the "Text File" then the "MS OLA DB..." in the work area. this set the direction of the pump. from the file to the SQL table

8. Double-click on the arrow between the two connections that was created by using the "Transform Data Task"

9. The source and destination tabs are already set by doing the steps prior, but still take a look at them to be sure.

10. goto "Transformations" and see if the fields from the text file are matching up w/ the table. If not, delete the associations and redefine them.

11. The "Lookups" tab doesn't seem to be needed w/ you app

12. goto the options tab and set the name and path for the exception file to be generated. good idea to do this w/ all packages created so you have some info to go on in case it fails.

13. there are of course other setting in the options tab you may or may not want to set. hit the help if you have any questions.

14. From here you are pretty much finished.
- Save the package
- the hit the green arrow on the menu bar to execute.

Good Luck!!!





Thanks

J. Kusch

 

[pto]

Thanks for the example. I will try today.

 

[pto]

J. Kusch,

Thank you so much!!! It worked and I feel so great. Again thanks alot.