August 26, 2009 at 1:24 pm
Hi there
I was wondering if anyone had some experience here. I'm importing around 14,000 XML files using the XML Source component in Data Flow Tab. I'm then doing some Data Conversion and pushing the data into a SQL Server Destination. No problems here, I can get data in.
When I join the XML Source tab to the SQL Server Destination tab it asks for an Output and an Input with the message "The source or destination component contains multiple inputs or outputs. Select AN input and AN output to connect the components".
My source XML file includes around 25 Input sources due to the relational nature of the data collection and files. I believe this means that I have to have many SQL Server Destinations (one to deal with each Input)? Is there a better way to map data between the relational structure of an XML file (the 25 Inputs) and a SQL Server Destination (Output)? I.e. so that multiple inputs and outputs can be mapped in one single step?
Any help is appreciated.
Many thanks.
Mat
🙂
August 26, 2009 at 3:32 pm
The XML source adapter, as you've seen, breaks the XML up into it's relational components. So you'll need a destination for each output.
You may be able to merge join the source outputs back into one dataset, but doing that for 25 outputs may get pretty messy.
The other option would be to not use the XML source. Try to read the file contents in as one TEXT column from a flat file adapter. You could then pass that into your destination and handle shredding the XML in the database engine.
Just curious though, if the XML Source is seeing 25 different relational nodes, shouldn't that be landing in multiple (maybe not 25) destinations in the DB anyways?
August 27, 2009 at 2:53 am
Hi John and thanks for your comments.
I will have a look at the shredding option as you suggest.
On the XML Source method. I can now see what you mean. The Inputs and Outputs Properties box (right click Show Advanace Editor) lists the various input and output fields for the 25 relational nodes. The system seems to have mapped these to a high degree of accuracy. YAY!
I guess the question now is can I take a single step (or a single pathway) to upload this data into a SQL Server Destination. Or do I need to have one destination per relational node (i.e. it would look like a flattened hierarchy with one XML Source and 25 green arrows to 25 different SQL Server Destinations)?
Any advice it appreciated.
Regards.
Mat:-)
August 27, 2009 at 9:55 am
Mat (8/27/2009)
I guess the question now is can I take a single step (or a single pathway) to upload this data into a SQL Server Destination. Or do I need to have one destination per relational node (i.e. it would look like a flattened hierarchy with one XML Source and 25 green arrows to 25 different SQL Server Destinations)?
What does your destination look like? How many tables will this data be landing in?
August 28, 2009 at 12:05 am
Hi Mat,
There are at least two ways to go here, and the correct answer is whichever way works best for you.
One way involves "flattening" the hierarchy of the XML into a relational model. That's what you're seeing now when you look at all the outputs from the XML Source Adapter. (This can also be done in a Script Component, using a utility from the .Net SDK named XSD.exe - more information is available at http://msdn.microsoft.com/en-us/library/x6c1kb0s(VS.80).aspx).
A second method involves loading the individual XML documents into a column of the xml data type. You can learn more about that at http://msdn.microsoft.com/en-us/library/ms189887(SQL.90).aspx.
Hope this helps.
:{> Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply