SSIS - Shred data from XML file in folder into columns of a table

This article is about shredding data from XML file in a folder into columns of a table using SQL Server Integration services (SSIS) .

Example :

Step 1 : Drag-drop Data Flow Task on to the Control Flow tab

Step 2 : Double-click on Data Flow Task , We will be directed to the Control Flow tab

Step 3 : In the Control Flow tab  , drag - drop
XML Source , Data Conversion and OLE DB Destination tasks .

Consider I have XML file  named Stud.xml in the path D:\

<StudentInfo>
  <Student>
    <Id>1111</Id>
    <Name>Sathya</Name>
  </Student>
  <Student>
    <Id>1112</Id>
    <Name>Deepak</Name>
  </Student>
</StudentInfo>


Step 4 : Double-click XML Source task and do the settings as shown in below image :




If you have schema within the XML document then you can the option Use inline Schema
or if you have schema file , you can browse to that path else click on Generate XSD ... to generate
schema file for the XML document .

Step 5 : Double-click on Data Conversion task and do the required data type conversion as shown in below image :

Step 6 : Create table in the AdventureWorks2012 database

CREATE TABLE StudentInfo (Id INT,Name CHAR(20))

Step 7 : Double-click on OLE DB Destination task and follow the settings as shown in below image :and then do the Column Mappings
[ note : provide server name and database name where you have created table ]