SQL Server file system objects
Posted: 08-18-2004 06:23 PM
Hello World!....

Here is the scenerio..

SQL Server Database
VB FrontEnd App.
App loads XML file data into database.

Changes are often made to the XML files. When changes are made,
the files must be reloaded into the database via the application. Changes
have been missed causing loss of production and jobs being processed
incorrectly.

I am looking for a script that will check the filedate of the
xml file or the filedate tag in the xml against a 'DesignFileDate' field in
the database. If a discrepancy in the filedates is identified, I want to
load the XML data to a temporary table [ExchangeTable] into the database.

The developer will provide a process to either load the data
into the production database or delete the data. This will be processed from
the Exchange table.

Having no background in programming languages and fairly new to
SQL this is quite a challenge.

Any assistance is greatly appreciated..

Regards

In message <ulY5uCjhEHA.140 (AT) TK2MSFTNGP12 (DOT) phx.gbl>, Steve T.
<stornari (AT) nvrinc (DOT) com> writes
You can use the file system object class to check file dates, and if you
store the last file date in a table or similar you can compare the two.
Another option would be to use a file watcher object, easily available
in .Net, such that it fires the process whenever a new file is detected.

As for loading XML data, then the SQLXML 3.0 Bulk Load tool would
probably work, but that depends on the structure of the XML document.
This is available as a web release from MS.

None of this really requires DTS, but could be encapsulated in a DTS
package. If you have no programming skills yourself then I suggest you
find somebody who does, as it will require coding.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org