July 6, 2017 at 2:58 am
Hi,
Bit of a broad one this so I'll start at the beginning.
We have a third party database which stores information for products in an XML format. The XML can be in a variety of formats depending on the product but all share some common bits.
We need to report on this data so at the moment we have an old .net program which shreds the XML data and puts it in a warehouse.
We are looking to upgrade our shredding process as what we would like is a kind of drag and drop shredder, imagine you say "this is my source column and drag it over to the the destination" (like in SSIS).
The issue with just using SSIS is if we want to add new columns we have to update the package.
So what I'm thinking of is this;
1. Does anyone know of any 3rd party tools which may allow us to do this? There must be something. This is my preference to be honest. One less headache for us.
2. Lets say we do it in SSIS and build a table to allow the mappings to be dynamic, is it possible to dynamically build the mapping in a data transformation tasks for the XML? or would I have to build an xsd for each and then just flip between the one we want and then map it.
Any advice would be appreciated. Perhaps there is a better way to do this.
Thank you for taking the time to read this.
Nic
July 6, 2017 at 3:03 am
My normal approach for this is to shred the XML using XQuery in SQL Server, much more flexible than SSIS
😎
Don't use much 3rd party tools so unfortunately I cannot help you on that one.
July 6, 2017 at 3:09 am
Hi,
Thanks for getting back to me, very useful, I didn't think of XQuery, I could built that up dynamically. Worth a look at before the SSIS approach.
So thank you very much.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply