DTS Package Fails When Scheduled

[kwilliams]

I have a DTS package that does the following:1) Drops existing database data from Server B2) Imports updated DB data from Server A to Server B3) Exports "current" table data into XML file 1 via ActiveX Script4) Exports "archive" table data into XML file 2 via ActiveX ScriptAll of these steps run fine when I run them manually in Enterprise Manager (SQL Server 2000), but the last 2 ActiveX Script steps fail when scheduled. I'm including the code below. If anyone can see what I'm doing wrong, and canhelp me to fix this problem, that would be great. Thanks.ActiveX Script (current):

'**********************************************************************'  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 "B:\dbdata_current.xml"	Main = DTSTaskExecResult_SuccessEnd Function

ActiveX Script (archive):

'**********************************************************************'  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 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 "B:\dbdata_archive.xml"	Main = DTSTaskExecResult_SuccessEnd Function

[aspnetguy]

This should help you outhttp://support.microsoft.com/default.aspx?...kb;EN-US;269074It is most likely to do with security. Running the DTS package manually runs under different security then when the package is a scheduled task.

[kwilliams]

This should help you outhttp://support.microsoft.com/default.aspx?...kb;EN-US;269074It is most likely to do with security. Running the DTS package manually runs under different security then when the package is a scheduled task.

<{POST_SNAPBACK}>

Thanks aspnetguy,When I researched the article you referenced at http://support.microsoft.com/?kbid=269074, I made some changes. I've made sure that the ownwer is set to "sa", that the job and the xml file to be outputted on the same server, and that the network path is set correctly (\\SERVERNAME\DIRECTORY\PAGENAME.xml). But I'm still getting that error when I schedule the job.I am able to run this ActiveX Script manually from my machine with no problem. But when I schedule the job, it fails and I receive this error message:Executed as user: SERVERNAME\sqlservice. ...t: DTSStep_DTSActiveScriptTask_1 DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE) Error string: Error Code: 0 Error Source= msxml3.dll Error Description: The network path was not found. Error on Line 121 Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 Error Detail Records: Error: -2147220482 (800403FE); Provider Error: 0 (0) Error string: Error Code: 0 Error Source= msxml3.dll Error Description: The network path was not found. Error on Line 121 Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: The network path was not found. Error source: msxml3.dll Help file: Help context: 0 ... Process Exit Code 1. The step failed.So I had my Network Administrator attempt to schedule the job directly from the server, and he wasn't able to run it manually or with a scheduled job. He obviously has complete rights to everything, so I'm not sure why he wouldn't be able to run it.If you or anyone can give me some advice on what could be causing this error, and how I can solve it, it would be very much appreciated. Thanks.