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.
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.
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?
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.
17 People are following this question.
