Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
| Search | Today's Posts | Mark Forums Read
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old December 15th, 2005, 01:55 PM
Banned
Points: 1,561, Level: 16
Points: 1,561, Level: 16 Points: 1,561, Level: 16 Points: 1,561, Level: 16
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2005
Location: , , .
Posts: 317
Thanks: 0
Thanked 0 Times in 0 Posts
Default DTS Package Fails When Scheduled

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
  #2 (permalink)  
Old December 15th, 2005, 06:54 PM
Banned
Points: 1,561, Level: 16
Points: 1,561, Level: 16 Points: 1,561, Level: 16 Points: 1,561, Level: 16
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2005
Location: , , .
Posts: 317
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #3 (permalink)  
Old December 19th, 2005, 06:45 PM
Banned
Points: 1,561, Level: 16
Points: 1,561, Level: 16 Points: 1,561, Level: 16 Points: 1,561, Level: 16
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2005
Location: , , .
Posts: 317
Thanks: 0
Thanked 0 Times in 0 Posts
Default

 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
  #4 (permalink)  
Old December 22nd, 2005, 08:18 PM
Friend of Wrox
 
Join Date: Dec 2005
Location: , AZ, .
Posts: 146
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #5 (permalink)  
Old December 23rd, 2005, 02:47 PM
Banned
Points: 1,561, Level: 16
Points: 1,561, Level: 16 Points: 1,561, Level: 16 Points: 1,561, Level: 16
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2005
Location: , , .
Posts: 317
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
DTS scheduled jobs not finishing abob SQL Server DTS 0 July 13th, 2007 07:48 AM
DTS package fails: [DBNETLIB]..... rrhandle SQL Server DTS 0 May 19th, 2005 07:29 PM
DTS Package Neither Succeeds Nor Fails Crowandazi SQL Server DTS 1 March 21st, 2005 11:27 PM
Scheduled DTS package does not work niravp SQL Server DTS 5 May 26th, 2004 11:38 AM
Scheduled Backup fails ppenn SQL Server 2000 2 December 3rd, 2003 12:21 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.