Import an XML file into a SQL table with SSIS

Tutorial to import an XML file into a SQL Server table with an SSIS package.

To import a file in XML format into a SQL Server table with SSIS, it is necessary to provide the schema in XSD format that represents the structure of the file. In this case, the XSD file is automatically generated by Visual Studio and Integration Services. This tutorial explains step by step how to import an XML file into a SQL table with the native XML Source component.

1. Prepare the SQL table with SSMS and the XML file to import

The destination table is the customer table with a basic structure. Here is the SQL Server code to create it from a connection to the target database with SSMS.

-- Create the client table
CREATE TABLE [dbo].[Customers](
   [CustomerID] [int] NOT NULL,
   [FirstName] [nvarchar](20) NULL,
   [LastName] [nvarchar](20) NULL,
   [City] [nvarchar](20) NULL,
   [Country] [nvarchar](50) NULL,
   CONSTRAINT [CustomersPrimaryKeyCustomerID] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
);
GO

Download the XML file used in this tutorial from the link below. Due to restrictions, the file is renamed to txt and with comments. So, rename the file to Customers_Data.csv and remove the comments, i.e. delete the first and last line of the file.

In case you have trouble downloading and using the file, here is a sample of the file in XML format with only the first 6 lines.

<?xml version="1.0" encoding="UTF-8"?>
<Customers>
   <Customer>
      <CustomerID>1</CustomerID>
      <FirstName>Ali</FirstName>
      <LastName>Ahmed</LastName>
      <City>Cairo</City>
      <Country>Egypt</Country>
   </Customer>
   <Customer>
      <CustomerID>2</CustomerID>
      <FirstName>Johnny</FirstName>
      <LastName>John</LastName>
      <City>Toronto</City>
      <Country>Canada</Country>
   </Customer>
   <Customer>
      <CustomerID>3</CustomerID>
      <FirstName>John</FirstName>
      <LastName>Doe</LastName>
      <City>Mexico City</City>
      <Country>Mexico</Country>
   </Customer>
   <Customer>
      <CustomerID>4</CustomerID>
      <FirstName>Shu</FirstName>
      <LastName>Abbas</LastName>
      <City>Paris</City>
      <Country>France</Country>
   </Customer>
   <Customer>
      <CustomerID>5</CustomerID>
      <FirstName>Jeane</FirstName>
      <LastName>Raffin</LastName>
      <City>Liushutun</City>
      <Country>China</Country>
   </Customer>
   <Customer>
      <CustomerID>6</CustomerID>
      <FirstName>Legra</FirstName>
      <LastName>Leate</LastName>
      <City>Błaszki</City>
      <Country>Poland</Country>
   </Customer>
</Customers>

2. Configure the XML source file from the SSIS Data Flow

First create an SSIS package in a project, and add a data flow into it. Then add the XML Source and OLE DB Destination components to the SSIS data flow.

Open the XML component and point to the XML file.

1. Select the path of the XML file to import: C:\data\Customers_Data.xml
2. Use Generate XSD and choose the path of the file: C:\data\Customers_Data_VisualStudio.xsd
3. Click on Columns to check the mapping

The XSD schema does not indicate the length of the fields by default and the package therefore assigns the DT_WSTR type with a length of 255 characters. This is the SSIS data type (DT_WSTR,255). The package therefore displays this warning message.

Warning at {EF1A3256-9D27-4D85-9F3A-570513FF215F} [XML Source [69]]: No maximum length was specified for the XML Source.Outputs[Customer].Columns[FirstName] with external data type System.String. The SSIS Data Flow Task data type “DT_WSTR” with a length of 255 will be used.

3. SSIS error No maximum length was specified for the XML Source

This is only a warning message, but it is possible to correct the problem. To do this, modify the XSD file and specify the lengths for all text fields. The XSD file generated automatically by Visual Studio is of the following type.

The XML element line does not contain the maxLength property. Replace each such line:

<xs:element minOccurs="0" name="FirstName" type="xs:string" />

With a block with the explicit length of the text field, for example:

<xs:element minOccurs="0" name="FirstName" >
 <xs:simpleType>
   <xs:restriction base="xs:string">
     <xs:minLength value="0"/>
     <xs:maxLength value="20"/>
   </xs:restriction>
 </xs:simpleType>
</xs:element>

The result is a file with the corresponding length for each column in the SQL table. So, the modified file shows the length of each XML text field, as in the picture below.

Save the new XSD file as C:\dataCustomers_Data.xsd. Finally point the SSIS XML component to the new and modified XML schema.

Once the warning is corrected, check the mapping of the columns in the source component.

4. Configure the connection to the destination SQL table

Open the OLE DB Destination component and choose the table in which to import the XML file. This is the dbo.Customers table created in step 1 of this tutorial.

5. Run the package to import the XML data into SQL Server

Finally, run the package and check that the data is inserted into the table without error with an SQL query from SSMS.

Use this SQL SELECT query to read the imported data from an SSMS connection.

SELECT *
FROM [dbo][Customers];

Conclusion on XML file import into SQL Server with SSIS

The XML Source component allows you to import an XML file into a MS SQL table with SSIS. The component is easy to use, especially with the option to automatically generate the XSD schema directly from the XML file provided. However, to work in control and avoid warnings and truncations, make manual adjustments, especially on the length of text fields.

Other tutorials on data integration with SSIS

• Import a CSV file with SSIS
• Import an Excel file with SSIS
• Tutorial to import multiple CSV files with SSIS

SSIS Tutorials