How to import multilevel xml files using SSIS

Question

Hello,

I have hundreds of XML files stored in a folder, and new ones are uploaded to this folder from the PLC every morning. I want to use SSIS to automate the process of importing these XML files into the SQL database into separate tables and create relationships between them, thus I would like to treat that when importing a column with a unique value added to the record e.g. index.

Here are 3 examples of XML files:

3I6LAKPVSLXSK316_FLX101_202164_152620_result.xml

4K31651225XSK316_FLX101_2020817_124427_result.xml

4K31651225XSK316_FRX101_2020817_123918_result.xml

I tried this way, but unfortunately, I was not able to add a column with an index when importing using the Derived

Column.

Here is the result of my imported data in SQL:

In the Derived column Im not able to setup a data type or use system variable "RowNumber" in expression.

My goal is to automate this import process of importing XML to SQL and have the data in the database in the best possible structure so I can use Power BI to connect to it and create a report from it.

Thank you

Answer 1

Add a File System task to the Control Flow of the package. Set the source folder to the folder where your XML files are stored.

Add a Foreach Loop Container to the Control Flow.

Connect the File System task to the Foreach Loop Container.

In the Foreach Loop Editor, configure the Enumerator to "Foreach File Enumerator" and set the source folder to the folder where your XML files are stored. Set the files filter to "*.xml" to only process XML files.

Add a Data Flow task inside the Foreach Loop Container.

Inside the Data Flow task, add an XML Source component. Configure it to read data from the current XML file being processed by the Foreach Loop Container.

Add a Flat File Destination component for each table you want to import the XML data into. Connect the XML Source component to each Flat File Destination component.

Configure each Flat File Destination component to load the XML data into the corresponding table in the SQL database. You can map the columns from the XML source to the columns in the destination table. If you want to add a unique index column, you can use a Derived Column transformation to generate a unique value for each record.

If you need to create relationships between the imported tables, you can use the Lookup transformation in the Data Flow task to lookup values from one table and match them with values in another table. Then, you can use a Conditional Split or a Multicast transformation to direct the data flow based on the matched values.