November 5, 2008 at 7:58 am
Hi, I've seached the web for an answer to this, but I cant find it unfortunately.
I would like to import some XML files from a directory on the network, to a SQL Server 2005 table on a daily basis.
Psudocode:
1.While there is a file in the directory on the network
a.Insert the file into table in SQL Server 2005(XML datatype) + insert ID, timestamp and filename
2.if import OK, delete file
3. If import failes, copy file locally to directory + error message.
Any help welcomed.
Obs.
November 5, 2008 at 9:02 am
best approach would be to setup an SSIS package.
use SQL server agent to run it as needed (daily, etc)
Have the package look for new XML files, open up, read as needed, and then save the data to the required tables.
The more you are prepared, the less you need it.
November 6, 2008 at 12:45 am
I've tried the SSIS approach, but keep running into problems ( data conversion problems, flat file source doesn't allow XML file as input)
I came to the comclusion that TSQL would be the solution, but if anyone can convince me otherwise, l'll give SSIS another go...
Obs.
November 6, 2008 at 7:37 am
Have you used the XML Task?
And you should be using the File not the Flat File data source.
Start with the XML Task, and then use that to make the file connection.
The more you are prepared, the less you need it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply