Extensible Markup Language (XML) is a robust method of transferring data between systems. However, for beginners, it can be confusing and difficult to implement. Fortunately, SQL Server Integration Services (SSIS) provides a simple solution with the “XML Source” data flow component.

The “XML Source” component in SSIS makes it trivial to read XML data into an SSIS package, allowing even XML newbies to hit the ground running. In this article, we will walk through the process of importing XML data into a SQL Server staging table using SSIS.

Package Setup

To get started, you will need to set up the SSIS package and the necessary database table. Follow these steps:

1. Add the provided XmlSourceDemo.dtsx file to your SSIS project as an existing package.
2. Use the provided create_people_xml.sql file to create a database table named “people_xml” in SQL Server Management Studio.
3. Edit the OLE_DB_Connection connection manager in the SSIS package to point to the database containing the “people_xml” table. Make sure to give this connection read/write permissions.
4. Copy the “people.xml” file to your hard drive.
5. Double-click the “Read XML File” data flow task in the SSIS package to enter the “Data Flow” tab.
6. Double-click the “XML Source” component and browse to the “people.xml” file in the “XML location” text box.
7. Click the “Generate XSD” button and save the “people.xsd” file to the same subdirectory as the “people.xml” file.
8. Browse to the “people.xsd” file in the “XSD location” text box.
9. Save and run the package.

Package Overview

Before we dive into the details, let’s take a look at the overall structure of the SSIS package:

The package consists of several components, including global variables, data flow tasks, and script tasks. These components work together to read XML data and insert it into the staging table.

Here is an example of the XML file that the package will read:

<people>
  <person>
    <name>John Doe</name>
    <age>30</age>
  </person>
  <person>
    <name>Jane Smith</name>
    <age>25</age>
  </person>
</people>

During execution, the package will display each individual XML record in a message box before inserting it into the staging table. Here is a screenshot of the package displaying a record from the XML file:

Once the XML data is safely stored in the staging table, it becomes ordinary SQL Server data that can be processed in familiar ways.

How it Works

Now, let’s take a closer look at how the SSIS package reads and processes the XML data:

1. The “XML Source” component is responsible for reading the XML file. It uses the XML Schema Definition (XSD) file to understand the structure of the XML data.
2. The XML Source component is configured through the Connection Manager. Here, you can specify the XML file and generate the XSD file.
3. The generated XSD file defines the XML fields that will be used in the package.
4. The “Recordset Destination” component is used to store the XML records in a variable called “xmlRecords”.
5. The “Loop through and shred XML records” foreach loop container is used to iterate through each XML record in the “xmlRecords” variable.
6. The “Display XML record” script task is responsible for displaying the XML record in a message box. This task can be removed or disabled if not needed.
7. The “Insert XML record into staging table” execute SQL task inserts the XML record into the staging table using parameterized SQL statements.

Once the XML data is in the staging table, you can perform any further processing or analysis using familiar SQL Server techniques.

Conclusion

In this article, we have explored how to import XML data into a SQL Server staging table using SSIS. The “XML Source” component in SSIS makes it easy for beginners to work with XML data and leverage the power of SQL Server.

By following the steps outlined in this article, you can quickly set up an SSIS package to read XML data, display it, and insert it into a staging table. Once the data is in the staging table, you have the flexibility to perform any further processing or analysis as needed.

With SSIS and SQL Server, working with XML data becomes a seamless and efficient process.