DTS Package Fails When Scheduled

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 Fails When Scheduled

Author

Topic

kwilliams

194 Posts

Posted - 2005-12-15 : 12:53:38

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
-------------------
It's the end of the world as we know it...and I feel fine

kwilliams

194 Posts

Posted - 2005-12-15 : 17:58:27

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.

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

kwilliams

194 Posts

Posted - 2005-12-19 : 17:44:48

Ok, I guess that I spoke too soon.

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.

When I researched the possible causes, I found a pretty good article through MS at http://support.microsoft.com/?kbid=269074. 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.

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 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.

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