Jump to content

DTS Package Export Data to XML Problem


kwilliams
 Share

Recommended Posts

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 FailedWindows 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_SuccessEnd Function

Link to comment
Share on other sites

try replacing //SERVERNAME with the actul IP Address of the server.If this still fails try setting the EVERYONE account (I know this is a bad idea, but it is just for testing, as soon as you have tested it change it back) to Full Control on the folder(s)/file(s) you are trying to save to. This will determine if it is a permissions issue.Post your results if you still have trouble.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...