Solved

Filesystem data to SQL Server table ...

Posted on 2003-12-03
5
331 Views
Last Modified: 2007-12-19
Hi All.

How to scans "on-line" server filesystem directories (e.g. D:\Data\ and the subdirectories) and insert them to SQL server table? I need to have file list data as filename, modification date, size,... in SQL table.

I DON'T want to scan files every xy minutes but I want to create really "on-line" connection between filesystem and SQL server - when file is modified, data about this file in SQL Server record is changed immediately. Is there any system services or any COM components?

***

Or have anybody any tested script for scaning directory structure and insert data to table?

Thanks Martin
0
Comment
Question by:martin_mmj
  • 3
5 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9865239
don't know of 1

but
declare table #t (dirdata varchar(8000))
insert into #t
 exec sp_cmdexec "Dir D:\*.*"

should do what you want....


you can set up a linked server to read actual text files ...  its described in BOL...  
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 20 total points
ID: 9865893
Personally I would do this by having a .NET service using the filewatcher to which will raise events when any thing happens.  then you can post the relevent details into SQL from the Service.

I do a similar thing for processing inbound FTP files automatically.
0
 

Author Comment

by:martin_mmj
ID: 9866253
And "xp_getfiledetails" and "xp_dirtree"? Is it possible to use for this problem? How to use it?
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9866269
yes you could use these however, if as you are suggesting in your question "I DON'T want to scan files every xy minutes "  then how do you invoke SQL to perform if in an event driven mannor?  you cant.    

which is why I suggest a service using the filewatcher events.











0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 10021186
Has your question been answered?   If so could you please close it.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question