Using Data Transformation Services: Can We Transfer XML Data? - Implementing DTS Transfer of XML
(Page 2 of 5 )
I will be using the SQL 2000 server, and will be running a simple select query with the forXML clause. This query will be a part of the ExecuteSQL task in the designer. The result of this query, which is but a single column (line, if you want to call it that) will be referenced to a Global Output Variable. I also fashion an ActiveXScript that will read this recordset (referenced by the Global variable) invoking ADODB and then save the result (persist) to an external XML file on my hard drive. I will also be using a workflow to make sure that the query is executed before the ActiveXScript is run. Please refer to my tutorials on DTS with Global variables. For each of these steps, there will be supporting screen shots. The overall designer view is shown in the next picture.
Description of Table used
In order to implement data transfer, I will be using a very simple table in my test bed database TestWiz. This sample has very few elements with no offending characters that you may sometimes find in tables. The next picture shows the table used.
Query Analyzer Result
The query that will produce the XML data used in the ExecuteSQL Task is shown in the next picture, together with the result from the Query Analyzer. Notice the first line, which is normally the column name while returning records from a relational table.
Next: DTS Design Details >>
More Database Articles
More By Jayaram Krishnaswamy
/ 10 
