DTS Package Fails When Scheduled

kwilliams · December 15th, 2005, 01:55 PM

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):

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 "B:\dbdata_current.xml"

    Main = DTSTaskExecResult_Success
End Function

ActiveX Script (archive):

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

kwilliams · December 15th, 2005, 06:54 PM

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

kwilliams · December 19th, 2005, 06:45 PM

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

David_the_DBA · December 22nd, 2005, 08:18 PM

I presume you have SQLXML3 SP3 installed? if not use this link to get it
http://www.microsoft.com/downloads/d...DisplayLang=en

Your SQL Agent is running as a Domain user for example KWDomain\SQLService? or is it a Local user like KWSQLBox\SQLService? If it is a local user switch it to a domain user that is a member of the local administrators on the SQL Server.

Have you tried using a local path, i.e. c:\SQLExports\Releases.xml, that exists on the server (have an identical path on your computer as well for testing)? Does that work?

In any case you may find the following code to be more efficient as it allows the SQL Server to return the XML instead of doing it yourself in your VBScript code (the forum is forcing some of the code lines to wrap)

Function Main()

    Dim oConn
    Dim oComm
    Dim stOutput

     Set oConn = CreateObject("ADODB.Connection")
     Set oComm = CreateObject("ADODB.Command")
     Set stOutput = CreateObject("ADODB.Stream")

      stOutput.Open

      oConn.Open "provider=SQLXMLOLEDB;data provider=SQLOLEDB;data source=(local);initial catalog=Tempdb;Integrated Security='SSPI';"

      oComm.ActiveConnection = oConn
      oComm.Properties("ClientSideXML") = "True"

      oComm.CommandText = "SELECT release_id ,start_date ,end_date ,title ,information FROM dbo.Release Release FOR XML NESTED ,Elements"
      oComm.Properties("Output Stream").Value = stOutput
      oComm.Properties("xml root") = "Root"
      oComm.Execute , , 1024 ''adExecuteStream

      stOutput.SaveToFile "C:\SQL_BagOTricks\ExportXML2.XML" , 2 'adSaveCreateOverwrite

      stOutput.Close
      oConn.Close

      Set oComm = Nothing
      Set oConn = Nothing
    Set stOutput = Nothing

        Main = DTSTaskExecResult_Success
End Function

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

kwilliams · December 23rd, 2005, 02:47 PM

Thanks David_the_DBA for your detailed reply. I'm on vacation until Jan 3rd right now, so I'll look your suggestions over, and get back to you with my results. I really appreciate your help, and I hope that you have a great holiday season.

KWilliams

KWilliams