question

sql83den avatar image
sql83den asked

How to merge multiple XML files with same structure into a single XML file using ssis?

We receive multiple XML files (a.xml, b.xml, c.xml etc) from a vendor on a daily basis. These files have the same structure and I want to create an SSIS package that combines all these files and merge them to one single XML file (with naming convention FileName_sysdate.xml) on a daily basis.

I tried using XML task with Merge (Operation Type) but that only worked if you have 2 files. What other options do I have?

Thanks in advance.

ssisxmlmergessis script task
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

If you can do it with two files, you can do it with multiple files.
In your control-flow, get the filename of the first file. Then use a foreach-file container to loop through the rest of the files.
Inside the foreach-file container, put your xml-task and substitute the file-names of the second file in the xml-merge operation with the filename from the foreach-file container.

So basically your Source file and your Destination file is the same file, and then you loop over SecondOperand.
I haven't tested if it's possible to have Source and Destination as same file. If that's not possible, you'd just need a filesystem task as the second step in your foreach-file container. In the xml-task, use a temporary filename for destination, and then do:
- Delete SourceFile.
- Rename DestinationFile to the name of SourceFile.

While testing this out, you will want to backup all the files, so that you don't accidentally start overwriting files or file contents.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sql83den avatar image
sql83den answered

Hi @Magnus Ahlkvist

Thank you for your response. Please forgive me for my ignorance here and I should have mentioned this in my post.

Those XML source file names are unique for every single file and are in no particular order. Here are few examples of how those source files looks like:

For 09/08/2020:

1x35fff.xml, 3eftt45pp.xml, 6nbb45ts.xml

For 09/09/2020:

3rst66yy.xml, 8gh5mm.xml, 75hhtsrp.xml

These in no way to identify which one will be the first file on a given day. so, I am not sure if I can use Foreach loop in this case.

Any other suggestions?

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Unless you need the elements inside the final file in a specific order, the order of the files are not important. Just loop through them with a foreach container. You could start with an initial step to create an empty target file (not completely empty, it needs a root-element but it can be empty apart from that). Or start with just getting any of the files with a .xml-extension and move it out of the folder where the files are and save it as the initial destination file.
If you need to read the files in a specific order and there's no way to tell the order from the filenames, you will have to do it with a manual process.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.