Thursday, November 10, 2016

How to load an XML file into a database using an SSIS package

Step-by-step process:
1.    Create a table named dbo.Items using the script given under SQL Scripts section.
2.    Create an XSD file named Items.xsd in the folder path C:\temp\xsd using the content provided under XSD File section.
3.    Create three XML files namely Items_1.xmlItems_2.xml and Items_3.xml in the folder path C:\temp\xml using the content provided under XML Files section.
4.    On the package, create 3 variables namely FileExtensionFilePath and FolderPath as shown in screenshot #1.
5.    On the package's Connection Managers, create an OLE DB Connection named SQLServer to connect to the SQL Server Instance as shown in screenshot #2.
6.    On the Control Flow tab, place a Foreach loop container and a Data Flow Task within the Foreach loop container as shown in screenshot #3.
7.    Configure the Foreach Loop container as shown in screenshots #4 and #5.
8.    Double-click on the Data Flow Task to navigate to the Data Flow tab. Place an XML Sourcecomponent and an OLE DB Destination as shown in screenshot #6.
9.    Configure the XML Source as shown in screenshot #7 and #8. The XML file path will be retrieved from the variable FilePath. This variable will be populated by the Foreach Loop container.
10.  Configure the OLE DB Destination as shown in screenshots #9 and #10.
11.  Screenshots #11 and #12 show the package execution.
12.  Screenshot #13 shows the table data before the package execution. Screenshot #14 shows the table data after the package execution. The data in the table dbo.Items now contains the data present in three XML files.
Hope that helps.
SQL Scripts:

CREATE TABLE [dbo].[Items](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemNumber] [nvarchar](50) NOT NULL,
    [ItemName] [nvarchar](60) NOT NULL,
    [Price] [numeric](18, 2) NOT NULL,
CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

XSD File

<xsd:schema xmlns:schema="ItemsXSDSchema" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="ItemsXSDSchema" elementFormDefault="qualified">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
    <xsd:element name="Items">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element minOccurs="0" maxOccurs="unbounded" name="Item">
                    <xsd:complexType>
                        <xsd:sequence>
                            <xsd:element name="Id" type="sqltypes:int" />
                            <xsd:element name="ItemNumber">
                                <xsd:simpleType>
                                    <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                                        <xsd:maxLength value="20" />
                                    </xsd:restriction>
                                </xsd:simpleType>
                            </xsd:element>
                            <xsd:element name="ItemName">
                                <xsd:simpleType>
                                    <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                                        <xsd:maxLength value="60" />
                                    </xsd:restriction>
                                </xsd:simpleType>
                            </xsd:element>
                            <xsd:element name="Price">
                                <xsd:simpleType>
                                    <xsd:restriction base="sqltypes:numeric">
                                        <xsd:totalDigits value="18" />
                                        <xsd:fractionDigits value="2" />
                                    </xsd:restriction>
                                </xsd:simpleType>
                            </xsd:element>
                        </xsd:sequence>
                    </xsd:complexType>
                </xsd:element>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>

XML Files

Items_1.xml
<?xml version="1.0"?>
<Items xmlns="ItemsXSDSchema"
    <Item>
        <Id>1</Id>
        <ItemNumber>I2345343</ItemNumber>
        <ItemName>Monitor</ItemName>
        <Price>299.99</Price>
    </Item>
</Items>

Items_2.xml
<?xml version="1.0"?>
<Items xmlns="ItemsXSDSchema"
    <Item>
        <Id>1</Id>
        <ItemNumber>J1231231</ItemNumber>
        <ItemName>Mouse</ItemName>
        <Price>29.99</Price>
    </Item>
</Items>

Items_3.xml
<?xml version="1.0"?>
<Items xmlns="ItemsXSDSchema"
    <Item>
        <Id>1</Id>
        <ItemNumber>K0456212</ItemNumber>
        <ItemName>Keyboard</ItemName>
        <Price>49.99</Price>
    </Item>
</Items>