SSIS reading multiple xml files from folder

sql-server-2005, ssis

Solution

Here is a possible option which demonstrates how to load multiple XML files having same definition into an SQL Server table. The example uses `SQL Server 2008 R2` and `SSIS 2008 R2`. The example shown here loads three XML files into an SQL table using SSIS `Data Flow Task` with the help of `XML Source` component.

Step-by-step process:

- Create a table named `dbo.Items` using the script given under SQL Scripts section.

- Create an XSD file named `Items.xsd` in the folder path C:\temp\xsd using the content provided under XSD File section.

- Create three XML files namely `Items_1.xml`, `Items_2.xml` and `Items_3.xml` in the folder path C:\temp\xml using the content provided under XML Files section.

- On the package, create 3 variables namely `FileExtension`, `FilePath` and `FolderPath` as shown in screenshot #1.

- 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.

- 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.

- Configure the `Foreach Loop container` as shown in screenshots #4 and #5.

- Double-click on the `Data Flow Task` to navigate to the `Data Flow` tab. Place an `XML Source` component and an `OLE DB Destination` as shown in screenshot #6.

- 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`. Note: In later versions of Visual Studio, XML Source will error out due to the ValidateExternalMetadata property. To fix this, Single click "XML Source" then Properties and set the ValidateExternalMetadata to False.

- Configure the `OLE DB Destination` as shown in screenshots #9 and #10.

- Screenshots #11 and #12 show the package execution.

- 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>

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6:

Screenshot #7:

Screenshot #8:

Screenshot #9:

Screenshot #10:

Screenshot #11:

Screenshot #12:

Screenshot #13:

Screenshot #14:

Problem

Original source

Related problems

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