Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
Password Reminder
Register
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Display Modes
  #1 (permalink)  
Old December 15th, 2005, 12: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, 05: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, 05: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, 07: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, 01: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
 


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 10: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 11:21 AM



All times are GMT -4. The time now is 12:34 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.