Loading log files (text files) automatically in SQL Server

[newprog1]

Hi Everyone,

I'm new to this forum and also not the best at programming with SQL Server as I have recently started using it, and I have a problem I hope someone can help me with.

I'll set the scene first:

Basically we have a SQL Server Database on our telecoms switch, to which under the maintenance contract we have no direct access. Every 15 mins log files are downloaded in *.log files (text files) which contain data we can upload to our own databases for querying. The log files are on an ftp server.

Requirement :

In short I would like to know the best way to automatically update my own SQL Server database with the new log files as they appear or if there is some existing sooftware which allows this type of schedule and auto import. I would be very greatful if someone can give me some ideas with this problem as it would save me so much time.



Thanks in advance

 

[philhege]

I had this requirement with periodically-updated flat files. What I did was create a DTS package that handled a representative flat-file format. Then I saved the DTS to Basic, and drew that code into a VB project, where I tweaked it to run against all files in a specified directory.

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.

 

[fredericofonseca]

you would need to create a either schedulle job or a polling program to run on either the server or on another machine with access to both the SQL Server and the FTP share (and this should be a share!!!)


This job can be a small VB program that loops through the FTP share, and for each new file there it will process it (e.g. load it into SQL Server using either BCP or a standard ADO inserts), and then move it to an archive directory or just delete it.

Very easy to do if you have a VB compiler available at your company.
If not then any VB programmer will be able to do this for a very small fee.



NOTE: As you mentioned a FTP server, whatever process you choose will need to take in consideration that the file MAY still being downloaded when your processing program tries to load it into SQL Server.
My preferred solution here, on cases where I have a say on how the files are FTP'ed, is to do the ftp as follows

> put my_original_file my_dest_folder/my_dest_file_tmp
> ren my_dest_folder/my_dest_file_tmp my_dest_folder/my_dest_file.log

On the above example the processing program would only look for "*.log" files, and would ignore all "*_tmp".



As for VB code to do the above please search these forums from keywords "loop" "file System" "ADO" "insert" "BCP" and you will find plenty of code to help you.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com