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.
Answer by sql83den ·
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:
1x35fff.xml, 3eftt45pp.xml, 6nbb45ts.xml
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?
Answer by Magnus Ahlkvist ·
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.