Export data from an XML with multiple outputs to a destination with multiple tables

Question

What is the best way to take an XML with multiple levels and send it to a destination with multiple tables?

Specifically, I am pushing XML data to an OLE source linked to an access file.

Here's a sample XML Document.

<?xml version="1.0" encoding="iso-8859-1" standalone="yes"?>

<sample_xml xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<PRINT_BATCH>
		<COMMON_VARIABLES>
			<COMMON_VARIABLE>
				<SELECTION_CODE>000001</SELECTION_CODE>
				<LATE_CHARGE>% </LATE_CHARGE>
				<LATE_CHARGE_CR>
				</LATE_CHARGE_CR>
				<LATE_PERCENT>0</LATE_PERCENT>
				<STMSN1> BILLING INQUIRES AND REPAIR </STMSN1>
			</COMMON_VARIABLE>
		</COMMON_VARIABLES>
		<PRINT_ON_ALL_STMTS>
			<BILLING_PERIOD_FROM>20090401</BILLING_PERIOD_FROM>
			<BILLING_PERIOD_TO>20090430</BILLING_PERIOD_TO>
			<BALANCE_DUE_AFTER>0</BALANCE_DUE_AFTER>
		</PRINT_ON_ALL_STMTS>
	</PRINT_BATCH>
</BBT_Test>

In the access file, I would have the following tables and foreign keys:

• PrintBatch
  PBID - Primary Key
• CommonVariables
  CVSID - Primary
  PBID - Foreign
• CommonVariable
  CVID - Primary
  CVSID - Foreign
• PrintOnAllStatements
  POASID - Primary
  PBID - Foreign

etc. etc. etc...

Currently, I have multiple Data Flow tasks in my SSIS project. In each task, I handle one table\node mapping. However, I would like to do it all in one task. How would you recommend doing this?

Is there a way to send multiple outputs within one XML source to multiple inputs within one OLE destination?

Thanks for the help!

Answer 1

You could achieve this using a script componet with multiple outputs.
in the script component use XML Readr or XPATH to fetch the records and redirect to appropriate output.

You could check this tutorial on W3Schools for XPATH its good and simple.

http://www.w3schools.com/XPath/default.asp

---

Hope this helps !!
Please close the thread once answered
Sudeep      My Blog

Answer 2

The problem, though, is that all of the outputs go to the same Access database, but with multiple tables.

I suppose I could send it to a flat table and then massage the data into the correct tables later, but that would seem a little on the "error-prone" side of things. Is there not a way to tell an OLEDB destination to have multiple inputs?

Answer 3

Joe, you should be able to accomplish this with the XML Source in a data flow. It will have multiple outputs (one per parent element, I believe), and each output will have a key column that lets you match up the outputs (for the parent child relationship). You can send each output to a seperate destination component.

The problem that you'll encounter is with the foreing key relationships. Because SSIS processes rows in batches, you're not guaranteed to have the parent records inserted prior to the child records. One solution is to drop or disable the foreign key constraint prior to loading the tables, and add them back afterward.

---

John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com

Answer 4

Joe, you should be able to accomplish this with the XML Source in a data flow. It will have multiple outputs (one per parent element, I believe), and each output will have a key column that lets you match up the outputs (for the parent child relationship). You can send each output to a seperate destination component.

The problem that you'll encounter is with the foreing key relationships. Because SSIS processes rows in batches, you're not guaranteed to have the parent records inserted prior to the child records. One solution is to drop or disable the foreign key constraint prior to loading the tables, and add them back afterward.

---

John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com

The problem with this is the whole "Separate destination component".

I had already tried that, and while it works, it is terribly frustrating to have multiple destination items for the same database .

Is it possible to tell a single destination to accept multiple inputs ?

Answer 5

Not with the out of the box components, no. Each destination component requires you to point it to a specific table. This lines up with the way SQL works - typically an INSERT statement only targets one table.

You could write a custom component that would do this.

Also, you are talking about multiple destination components in the same data flow task, right?

---

John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com

Answer 6

Also, you are talking about multiple destination components in the same data flow task, right?

---

John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com

What is the best way to make sure foreign key fields will match up? Single flow task, or a flow task for each table?

Answer 7

"Best" depends on your perspective. If you disable and renable the FK constraints, you can do it in a single data flow task. That still forces the FKs to be valid, they'll just be checked at the end of processing).

If you want to leave the constraints intact, so that each row is checked as it is inserted, I'd recommend multiple data flows (one per destination table).

---

John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com

Answer 8

You may want to skip SSIS altogether and use the SQL Server function SQLXMLBulkLoad. This will take a single XML file and dump it into as many SQL tables as you need, though configuring the .xsd file to translate the XML can be a little tricky.

For a practical example take a look at http://www.bimonkey.com/2009/08/bulk-loading-xml-with-sqlxmlbulkload/

James

Answer 9

You may want to skip SSIS altogether and use the SQL Server function SQLXMLBulkLoad. This will take a single XML file and dump it into as many SQL tables as you need, though configuring the .xsd file to translate the XML can be a little tricky.

For a practical example take a look at http://www.bimonkey.com/2009/08/bulk-loading-xml-with-sqlxmlbulkload/

James

James,

I've used that technique before with SQL Server, and I completely agree.

However, I am trying to import the XML to access.

Answer 10

What you are proposing is impossible with an OLE DB Destination as it will accept only one input which will map to only one table.

You could use an OLE DB command to parse and direct the incoming data via some evil code or a stored procedure (with evil code), or perhaps use a script, but neither of those options apply to ODBC / Access.

Really your best option is to use SQLXMLBulkLoad into SQL Server and then shift it into access.

James

---

James Beresford @ http://www.bimonkey.com SSIS / MSBI Consultant in Sydney, Australia