SSIS Step by Step 004 - Output XML Data from database

SSIS Step by Step 004 - Output XML Data from database

Requirement & Preface

There's a table Order, we need to output its records to an XML file.

What's new in this post ?

1. How to output data to an XML file?

Steps

1. Create a new package - CH03-DEMO01-DataToXML.dtsx
We need to create two variables as screenshot shows:

FilePath - to define the location of output XML file.
OrderXMLString - All data from Order table will be saved as a XML string.

2. EST_DataToXML Task Editor

The ResultSet should be XML.
The SQL Statement should be:

SELECT [Order].OrderID,
       [Order].CustomerID,
       [Order].EmployeeID,
       [Order].Freight,
       [Order].OrderDate,
       [Order].RequiredDate,
       [Order].ShipAddress,
       [Order].ShipCity,
       [Order].ShipCountryRegion,
       [Order].ShipCountryRegionRegion,
       [Order].ShipName,
       [Order].ShipPostalCode,
       [Order].ShipRegion,
       [Order].ShipVia,
       [Order].ShippedDate
FROM dbo.[Order] AS [Order] FOR XML AUTO

3. Result Set Pane

We defined a string-type variable to this XML string returned by SQL query.

Please see this SQL

The reason why I process it in this way, because there's no XML destination tool in data flow. So I have to save all data into a string, then use script task to output it to an XML file.

4. Script task

Codes inside:

public void Main()

{

            string content = Dts.Variables["User::OrderXMLString"].Value.ToString().Replace("<ROOT>", "<?xml version=\"1.0\" encoding=\"utf-8\" ?><allOrders>").Replace("</ROOT>", "</allOrders>");

            string filePath = Dts.Variables["User::FilePath"].Value.ToString();

            StreamWriter writer = new StreamWriter(filePath);

            writer.WriteLine(content);

            writer.Close();

            // TODO: Add your code here

            Dts.TaskResult = (int)ScriptResults.Success;

}

5. Execute this package and check the output XML file

Segements of outputed XML file -

This output XML file will be the source of next demo.


Get all SSIS Step by Step, refer to SIMON'S SSIS Step by Step Please let me know if you have any questions about this blog. Contact to me by email - simonlvpin@yahoo.com Or Skype - simonlvpin