SSIS Step by Step 006 - Use XSLT to transform XML documents

Requirement & Preface

XSL stands for EXtensible Stylesheet Language, and is a style sheet language for XML documents.
XSLT stands for XSL Transformations. We have an XML file and want to transfer it to another style according to user-defined xslt document.

 

What's new in this post ?

1. How to use XML task to transfer XML style

Steps
1. Create a new package -CH04-DEMO01-XMLTask.dtsx

Drag an XML task to control flow.

2.  Edit it and create three XML file connection manager and configure then as below:

Operation Type - XSLT, we will use standard xslt document to transfer target XML file.

Source - U01-CH04-002-XMLDemo.xml

Codes:

<?xml version="1.0"?>

-<extract date="2007-12-05"> -<counters> -<counter name="server1" category="dispatcher"> <runtime>6</runtime> <queue>3</queue> <maxrequest>8</maxrequest> <color>blue</color> -<host> <name>svo2555</name> <path>\\dispatcher</path> <lastaccessed>2007-02-03</lastaccessed> </host> </counter> -<counter name="server1" category="gateway"> <runtime>1</runtime> <queue>10</queue> <maxrequest>10</maxrequest> <color>purple</color> -<host> <name>svo2555</name> <path>\\gateway</path> <lastaccessed>2007-02-03</lastaccessed> </host> </counter> </counters></extract>

Schema of xslt - U01-CH04-002-XMLSchema.xslt

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

        <xsl:output method="xml" indent="yes"/>

        <xsl:template match="/extract">

                <xsl:variable name="extractDate" select="/extract/@date" />   

                <counters>

                        <xsl:for-each select="counters/counter">

                                <counter>           

                                        <extractDate><xsl:value-of select="$extractDate"/></extractDate>           

                                        <category><xsl:value-of select="@category"/></category>           

                                        <name><xsl:value-of select="@name"/></name>           

                                        <runtime><xsl:value-of select="runtime"/></runtime>           

                                        <queue><xsl:value-of select="queue"/></queue>           

                                        <maxrequest><xsl:value-of select="maxrequest"/></maxrequest>           

                                        <color><xsl:value-of select="color"/></color>           

                                        <hostName><xsl:value-of select="host/name"/></hostName>           

                                        <path><xsl:value-of select="host/path"/></path>           

                                        <lastaccessed><xsl:value-of select="host/lastaccessed"/></lastaccessed>       

                                </counter>   

                        </xsl:for-each>   

                </counters>

        </xsl:template>

</xsl:stylesheet>

3. Execute package and get the output XML file

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

SSIS Step by Step 005 - Load data from XML file

Requirement & Preface

Load all data from an XML file.
The XML source is from the output of previous demo.
http://simonlv.blogspot.com/2012/08/ssis-step-by-step-004-output-xml-data.html

What's new in this post ?

1. How to load data from an XML file.

Steps

1. Create a new package  - CH03-DEMO02-XMLToData.dtsx and add this two component in control flow.

2.  In EXT_TruncateOrders, truncate table order first.

3.  Data flow Task - DFT_XML_DataToDB

Added a XML source in data flow task.
Notice - there's no XML destination task in data flow task but it has XML source.

4. In XML Source editor, the data access mode is 'XML file location', we select the location which is ouputed by previous demo.

The XSD location will be generated automaticlly when you select an XML location.

See the columns of XML file.

5.  ADO.NET Destination

 

 

6. Execute package and check the results

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

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