DTS Package Fails When Scheduled

[kwilliams]

I have a DTS package that does the following:
1) Drops existing database data from Server B
2) Imports updated DB data from Server A to Server B
3) Exports "current" table data into XML file 1 via ActiveX Script
4) Exports "archive" table data into XML file 2 via ActiveX Script

All 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_Success
End 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_Success
End Function

[kwilliams]

I figured out the problem...I forgot to change the path of the file to a global path vs. a local path. Once I did that, it worked. Thanks.