Using Data Transformation Services: Can We Transfer XML Data? - ActiveX Script Based Data transfer implementation

(Page 5 of 5 )

The following link here describes the ActiveXScript based exporting of XML to a file. I did not author this article. I have used the script, and made changes, so that I can look at the same example above -- the tiny little table, and the query. The script is as shown. In the DTS designer, drag and drop an ActiveXScript task and double click to open the scripting interface. Cut and paste this script. If you plan to use this script, make sure that you write out sSQL all in one line.

'**********************************************************************
'  Visual Basic ActiveX Script
'  Please refer to the original author here: http://www.sqlxml.org/
'  faqs.aspx?faq=10
'************************************************************************
Function Main()
Dim oCmd, sSQL, oDom
Set oDom = CreateObject("Msxml2.DOMDocument.4.0")
Set oCmd = CreateObject ("ADODB.Command")
oCmd.ActiveConnection = "Provider=<b>SQLOLEDB</b>;

Data Source=(local);Initial & _
Catalog=<b>TestWiz</b>; Integrated Security=SSPI"
sSQL = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'> _
<sql:query>select <b>fname</b> from <b>XMLDTSTest</b>  for xml auto _
</sql:query></ROOT>"
oCmd.CommandText = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties("Output Stream") = oDom
oCmd.Execute , , 1024
oDom.Save "c:\xmldtsjay.xml"
Main = DTSTaskExecResult_Success
End Function

The Result of this package

The following picture shows the browser display of the XML file xmldtsjay.xml created by the above script. Isn't this neat?

Summary

The short answer for the question posed in the title is not a total 'no', but a partial 'yes'. The script based solution appears to be independent of DTS except for the last line in the code. Does it work outside of DTS? I have not tested it yet. Moreover, it used a different Provider -- SQLOLEDB. But perhaps the reason it works so well is because of the DOM support, and the fact that the query resembles that of a template query. For other questions regarding the above code, I direct the readers to the link at the beginning of this section.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: Using Data Transformation Services: Can We Transfer XML Data?

· Here is another interesting tutorial on DTS as related dealing with XML. There are...   >>> Post your comment now!