sql server dts: reading a text file as it is written -- sql server dts

reading a text file as it is written

Greg Stigers, MCSA
1/20/2005 6:50:41 PM

sql server dts:

A remote office developed a PC-based app that writes its data to a text
file. We want to maintain this data at the main office, and in SQL Server,
due to the significance of the data. A typical day's file may have a couple
hundred records, written over an eight hour shift. Rather than changing the
application to connect to our SQL Server, and worrying about network
connectivity and impact on productivity, is there a reasonable means to have
SQL Server detect when this text file is updated, and add the most current
record(s) to our SQL Server table? Please also reply to my email address,
gregstigers@spamcop.net. Thanks.
--
Greg Stigers, MCSA
remember to vote for the answers you like

Re: reading a text file as it is written

John Gallardo [MSFT]
1/27/2005 5:41:24 PM

NT has file change notifications. You can write some C# to receive these
notifications and then kick off the script which updates the database.
Check out System.IO.FileSystemWatcher
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemiofilesystemwatcherclasstopic.asp)

However, reading the file while it is still open and being written to is
tricky... it actually depends on how the application doing the writing
opened the file. It can specify whether it wants to allow people to read it
while it is being written. If it said that it doesn't want to share with
anybody, then there isn't much you can do other than change that
application.

--
John Gallardo
SQL Server Engine
Microsoft Corp
[This posting is provided "AS IS" with no warranties, and confers no
rights.]

[quoted text, click to view]