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
animated405Asked:
Who is Participating?
 
Melih SARICAOwnerCommented:
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
 
Melih SARICAOwnerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
animated405Author Commented:

I mean I had already tried that before posting this thread
thanks
0
 
Melih SARICAOwnerCommented:
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 SARICAOwnerCommented:
opps sorry
wrong window.. explanationnn

let me try it 4 u

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.