Using Data Transformation Services: Can We Transfer XML Data? - The ActiveX Script

(Page 4 of 5 )

The ActiveXScript will glue the query result to the Global variable as well as persist the XML to an XML file. The following script is used in this task. The script instantiates (creates) an ADODB.Recordset, and to this the Global Variable is assigned. The Recordset is saved as an XML file. The scripting interface in DTS is very strict; make sure that the syntax is as close to the one shown as possible.

'**********************************************************************
'  Visual Basic ActiveX Script
' Jayaram Krishnaswamy
'************************************************************************
Function Main()
dim RS
set RS = CreateObject ("ADODB.Recordset")
set RS = DTSGlobalVariables("NewXMLDTS").value 
rs.save "C:\Documents and Settings\computer user\Desktop\Nov7\

Newxml.xml", _
adPersistXML
Main = DTSTaskExecResult_Success
End Function

Also a Workflow item is added to make the script run only after a successful run of the ExecuteSQL task. The package is saved after making sure none of the individual items are not highlighted.

DTS Package run results

This package runs successfully and a file will be created in the location where it is saved. If this file is displayed in the browser, what you will see is the following:

Source view of browser display

This is pretty ugly and useless. Now let's take a look at the source view of this browser output. You will see the following. In addition to what was seen in the Query Analyzer result, other offending characters may also be present. The source view may be cleaned up through some filtering, but this is not a clean solution.

Next: ActiveX Script Based Data transfer implementation >>
 

More Database Articles
More By Jayaram Krishnaswamy

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!