View Single Post
  #1 (permalink)  
Old January 3rd, 2006, 01:17 PM
kwilliams kwilliams is offline
Banned
Points: 1,561, Level: 16
Points: 1,561, Level: 16 Points: 1,561, Level: 16 Points: 1,561, Level: 16
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2005
Location: , , .
Posts: 317
Thanks: 0
Thanked 0 Times in 0 Posts
Default DTS Package Export Data to XML Problem

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
Code:
'**********************************************************************
'  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