DTS Package Export Data to XML Problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS Package Export Data to XML Problem

Author

Topic

kwilliams

194 Posts

Posted - 2006-01-03 : 12:16:42

I have a DTS Package that uses an ActiveX Script using DOM to pull data from a database table, put XML tags around each field, and save that XML-formatted data to an XML file on the same server. I've included the ActiveX Script at the bottom of this post.

It works great when I have the data saved to a local path, but when I try to change it to a network path, I get this error message:

Windows - Delayed Write Failed
Windows was unable to save all the data for this file \SERVERNAME\DIRECTORY\docs\xml\sample.xml. The data has been lost. The error may be caused by a failure of your computer hardware or network connection. Please try to save this file elsewhere.

I think that the problem may be whether or not the SQL Server Agent has read/write permissions on that server, but I'm not sure how to set this up. If anyone could help me out with this process, that would be great. Thanks for any help.

ActiveX Script


'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Function Main()
	Dim objADORS
	Dim objXMLDoc

	Dim nodeRoot
	Dim nodeTemp
	Dim nodeRelease

	'Create ADO and MSXML DOMDocument Objects
	Set objADORS = CreateObject("ADODB.Recordset")
	Set objXMLDoc = CreateObject("MSXML2.DOMDocument.3.0")

	'Run the stored procedure and load the Recordset
	objADORS.Open "SELECT * FROM tblTABLENAME WHERE (start_date < GETDATE()) AND (end_date > GETDATE())", _
		"PROVIDER=SQLOLEDB.1;SERVER=SERVERNAME;UID=UID;PWD=PWD;DATABASE=DBNAME;" 

	'Prepare the XML Document
	objXMLDoc.loadXML "<root />"
	Set nodeRoot = objXMLDoc.documentElement

	'For each record in the Recordset
	While Not objADORS.EOF
		
		Set nodeRelease = objXMLDoc.createElement("release")
		nodeRoot.appendChild nodeRelease

		Set nodeTemp = objXMLDoc.createElement("release_id")
		nodeTemp.nodeTypedValue = Trim(objADORS.Fields("release_id").Value)
		nodeRelease.appendChild nodeTemp

		Set nodeTemp = objXMLDoc.createElement("start_date")
		nodeTemp.nodeTypedValue = Trim(objADORS.Fields("start_date").Value)
		nodeRelease.appendChild nodeTemp

		Set nodeTemp = objXMLDoc.createElement("end_date")
		nodeTemp.nodeTypedValue = Trim(objADORS.Fields("end_date").Value)
		nodeRelease.appendChild nodeTemp

		Set nodeTemp = objXMLDoc.createElement("title")
		nodeTemp.nodeTypedValue = Trim(objADORS.Fields("title").Value)
		nodeRelease.appendChild nodeTemp

		Set nodeTemp = objXMLDoc.createElement("information")
		nodeTemp.nodeTypedValue = Trim(objADORS.Fields("information").Value)
		nodeRelease.appendChild nodeTemp

		objADORS.moveNext
	Wend

	objADORS.Close
	Set objADORS = Nothing

	'Save the created XML document
	objXMLDoc.Save "\\SERVERNAME\DIRECTORY\docs\xml\XMLFILE.xml" '<--THIS DOESN'T WORK
'	objXMLDoc.Save "B:\docs\xml\XMLFILE.xml" '<--THIS WORKS
	Main = DTSTaskExecResult_Success
End Function

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources