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

SSIS Step by Step 001 - Load data from a flat file

SSIS Step by Step 001 - Load data from a flat file

Requirement & Preface

We are going to load data from a flat file, data format in flat file as blow:

AddressID,AddressLine1,AddressLine2,City,StateProvinceID,PostalCode,SpatialLocation,rowguid,ModifiedDate

1,1970 Napa Ct.,NULL,Bothell,11,98011,NULL,9AADCB0D-36CF-483F-84D8-585C2D4EC6E9,2002-01-04 00:00:00.000

2,9833 Mt. Dias Blv.,NULL,Bothell,79,98011,NULL,32A54B9E-E034-4BFB-B573-A71CDE60D8C0,2003-01-01 00:00:00.000

3,7484 Roundtree Drive,NULL,Bothell,79,98011,NULL,4C506923-6D1B-452C-A07C-BAA6F5B142A4,2007-04-08 00:00:00.000

4,9539 Glenside Dr,NULL,Bothell,79,98011,NULL,E5946C78-4BCC-477F-9FA1-CC09DE16A880,2003-03-07 00:00:00.000

5,1226 Shoe St.,NULL,Bothell,79,98011,NULL,FBAFF937-4A97-4AF0-81FD-B849900E9BB0,2003-01-20 00:00:00.000

This file has a file header and multiple records, data in rows are delimited by a comma.

We load all data from this flat file into a table in database.

Steps

1.  Create a demo database, all demos in 'SSIS Step by Step' bases on this database, so you can use below script to create a demo database. 

---------------------------------------------------------------------------

-- Script U01-CH01-001

-- Create demo database

---------------------------------------------------------------------------

USE [master]

GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'SSISDemoDB')

DROP DATABASE SSISDemoDB

GO

USE [master]

GO

/** Make sure there's a folder (E:\Projects\Database) in your local machine,

    or you can change the folder name **/

CREATE DATABASE SSISDemoDB ON  PRIMARY

( NAME = N'SSISDemoDB_Data', FILENAME = N'E:\Projects\Database\SSISDemoDB_Data.mdf' , SIZE = 174080KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )

 LOG ON

( NAME = N'SSISDemoDB_Log', FILENAME = N'E:\Projects\Database\SSISDemoDB_Log.ldf' , SIZE = 18432KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )

GO

2.  Create a demo table which will save data from the flat file. This table will be used again in next blogs.

---------------------------------------------------------------------------

-- Script U01-CH01-002

-- Create demo table

---------------------------------------------------------------------------

USE SSISDemoDB

GO

 

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Address') AND type in (N'U'))

DROP TABLE [Address]

GO

CREATE TABLE [Address](

[AddressID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,

[AddressLine1] [nvarchar](60) NOT NULL,

[AddressLine2] [nvarchar](60) NULL,

[City] [nvarchar](30) NOT NULL,

[StateProvinceID] [int] NOT NULL,

[PostalCode] [nvarchar](15) NOT NULL,

[SpatialLocation] [geography] NULL,

[rowguid] [nvarchar](120) NOT NULL,

[ModifiedDate] [datetime] NOT NULL

)

GO

3.  Create a SSIS solution, choose Integration Services Project

4.  Change the package name and drag a Data Flow Task from Control Flow Items to Control Flow.

     And change the data flow task name to be DFT_LoadFlatFiles

5. Go into DFT_LoadFlatFiles and drag a Flat File Source to Data Flow.

  Change the name of flat file source to be FFS_PersonAddress.

  We need to click FFS_LoadFlatFiles and to build a flat file connection manager which is band to this component and connect to a flat file.

6.  See this Flat File Source Editor, we need to build a connection manager first, click the 'New' button.

7. Input descriptions for a connection manager first.

   Then browse the incoming file where the data exists.

   

   You can copy the data where I post in Requirement & Preface and save to a flat file, then browse and choose this file.

   Here's my test file.

  Notice the format - Delimited

  And choose Column names in the first data row. Because the testing file do have a data header.

8. Click Columns, see our Row delimiter should be {CR}{LF} and Column delimiter - Comma. 

    Actually, we have many choice, but in current flat file the column are delimited by Comma.

9. Click Advanced, we need to choose the proper data type for SSIS transformation. There's difference about the data type between SQL and SSIS.

    You can refer to http://msdn.microsoft.com/en-us/library/ms141036.aspx to know more about the SSIS data type.

    The table script for Person Address we can see here:

[AddressID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,

[AddressLine1] [nvarchar](60) NOT NULL,

[AddressLine2] [nvarchar](60) NULL,

[City] [nvarchar](30) NOT NULL,

[StateProvinceID] [int] NOT NULL,

[PostalCode] [nvarchar](15) NOT NULL,

[SpatialLocation] [geography] NULL,

[rowguid] [nvarchar](120) NOT NULL,

[ModifiedDate] [datetime] NOT NULL

    For NVARCHAR SQL Data type, we use Unicode string [DT_WSTR] in SSIS.

    For INT in SQL data type, we use four-byte signed integer.

   Other please see pictures.

   Besides, [SpatialLocation] [geography] NULL, we can't transfer it in SSIS directly, there's no mapping data type in SSIS, if we have to process it we need to write script to handle it. In this article, we aren't going to process this column.

 If want to know more about the spatial data, please Google it or refer to other article - http://www.sql-server-performance.com/2010/spatial-data-support-ssis/

    

After this, click OK.

10.  Back to Flat File Source Editor. See we have already built a Flat file connection manager.

       Click Columns where we can decide which columns we need to pass into next SSIS component.

   Sometimes, a flat file may include many columns, but we may only choose some columns to pass to next component and insert them to a table. 

   In this demo, we choose all columns to pass.

   Click OK, then back to Data Flow Design window. We can see there's a flat file connection manager instance exists in Connection Manager pane.

In data flow, we have created a 'Source', obviously this source is a flat file, then we are going to choose a destination component which should be a table.

11.  See Data Flow Destinations, drag a 'OLE DB Destination' to data flow and change name to be 'DST_PersonAddress'.

       Drag the green connection arrow from FFS_PersonAddress to connect to DST_PersonAddress, that means the columns we choose in FFS_PersonAddress will as an output columns pass to DST_PersonAddress. 

12. Double click DST_PersonAddress, edit it as blow ways.

      

     We need to built a connection from SQL Server to this component, so click 'New' to create a OLE DB Connection manager.

Because we use local machine, so we server name - localhost

Then we can choose the demo database from the list. 

Click OK, see we get a data connections from local server and demo database.

Click OK.

13. We can find another connection shows in Connection Manager Pane.

    So there're two connections.

    First connection - to build a connection from a flat file to FFS_PersonAddress

    Second connection  - to build a connection from a database table to DST_PersonAddress

    Between FFS_PersonAddress and DST_PersonAddress, we connect them and FFS_PersonAddress will pass the values from flat file to DST_PersonAddress, thus, data from flat file will go into the database table.

 Right click and execute this package.

Green..is OK. 

I will add error process in later tutorials. 

Validate data in database table, congratulation ! we done!