• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

Can a dts package export xml?


Hello,

Usually I only do text and excel files, but I have a need to transform a dataset into an xml file using a dts package. Having some trouble figuring this out. If anyone can point me in the right direction or provide a good resource, that would be most appreciated!

thanks in advance
0
animated405
Asked:
animated405
  • 4
  • 3
  • 2
2 Solutions
 
Melih SARICACommented:
in ur source Query use For XML Auto

it ll return a XML recordset

try adding FOR XML Auto at the end of ur Query in QA..

u ll see the XML output ..

then

write this XML output to a text file in with a DTS script



0
 
animated405Author Commented:

thanks, I found something using activex in the dts that should work. I tried as you suggested and all I got was a bunch of numbers that I couldn't make any sense of
0
 
animated405Author Commented:

I mean I had already tried that before posting this thread
thanks
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Melih SARICACommented:
using an activex wont solve ur problem ..

u need an executable .. to check ur desired directory for new arrivals..
then run ur dts pack..

to trigger with a new arrival ,the dts ll import and move the file into the processed directory

0
 
Melih SARICACommented:
opps sorry
wrong window.. explanationnn

let me try it 4 u

0
 
Melih SARICACommented:
this
dts activex script  would help u i guess


Function Main()
      dim mcon
      dim mobj
      dim mdata
      
      set mcon= createobject("Adodb.connection")
      mcon.connectionstring="driver={SQL Server};server=localhost;database=northwind;user=sa;password=pass"
      mcon.open()

      set mdata = mcon.execute("select * from products for xml auto")
      set mobj = createobject("scripting.filesystemObject")
      set mobj = mobj.createtextfile("c:\test.xml")
      do while not mdata.eof
            mobj.writeline(mdata(0))
            mdata.movenext                  
      loop
      
      set mdata= nothing
      mcon.close
      mobj.close
      set mobj = nothing
      set mcon = nothing

      Main = DTSTaskExecResult_Success
End Function
0
 
animated405Author Commented:

this is what I tried to use, just keeping it simple for the moment...

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
      Dim objADORS
      Dim objXMLDoc

      Dim nodeRoot
      Dim nodeOrder

      '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 "EXEC up_datafeed", _
            "PROVIDER=SQLOLEDB;SERVER=XXX.XXX.XXX.XXX;UID=XXX;PWD=XXX;DATABASE=XXX;"

      'Prepare the XML Document
      objXMLDoc.loadXML "<orders />"
      Set nodeRoot = objXMLDoc.documentElement

      'For each record in the Recordset
      While Not objADORS.EOF
            
                  Set nodeOrder = objXMLDoc.createElement("order")
                  nodeRoot.appendChild nodeOrder

            objADORS.moveNext
      Wend

      objADORS.Close
      Set objADORS = Nothing

      'Save the created XML document
      objXMLDoc.Save "C:\test.xml"

      Main = DTSTaskExecResult_Success
End Function


this is what I would expect to see:
<orders>
<order><order/>
<order><order/>
<order><order/>
<order><order/>
<order><order/>
<order><order/>
<order><order/>
<order><order/>
<order><order/>
<order><order/>
</orders>

this is what I get:

<orders>
<order/>
<order/>
<order/>
<order/>
<order/>
<order/>
<order/>
<order/>
<order/>
<order/>
</orders>


my recordset returns 10 rows, why does it write only closing the node?

thanks!



0
 
Anthony PerkinsCommented:
Those two Xml documents are identical.
0
 
Anthony PerkinsCommented:
Delete without a refund.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now