Load XML file in SSIS

Today we will learn how to load the XML data in SQL Server using SSIS.

Open the SSDT.

Take data flow task

Double click on data flow task.

In data flow, I am taking the source as XML Source

Double click on the XML Source

XML Source Editor will be open

In Connection Manager, we need to select the Data Access Mode.

Here I am select the XML file from the location.

This is my XML File

Now browse the file

If we don’t have the XSD file we need to generate the file.

Click on the Generate XSD button.

It will generate the XSD file. We need to save this file.

Save it.

Below XSD file generated

Now we need to browse this file

Now go to the Column table

When we select Column table we will get the below warning

Click ok.

The above warning stems from having the XSD file define string based columns which do not have a minimum and maximum length noted for that particular element within the XSD. This issue can be alleviated by adding text similar to the below notation to your string columns. To avoid this warning we need to add the min and max length in XSD file.

All column is mapped

Click ok.

Now we need to configure the Error output option.

Click on Ok.

XML source is configured

Now I am taking destination as OLEDB destination.

Configuring the OLEDB destination.

Now mapping the columns.

Now the package is ready to run.

No data in table

Now I am running this package.

Package executed successfully.

Now see the result in the database.