locked
load on premise xml to sql dw RRS feed

  • Question

  • Hi, I'm looking for some advice on the best way to get on premise (complex)xml's into azure sql dw

    So far the approach I was thinking is ADFv2 to trasnfer xml to blob storage, ADFv2 (ssis) to shred/load the data into azure sqldb (as i believe xquery is supported here), then loading to sql dw via polybase to perform all required transformations/aggregations in sql dw

    It just seems like a lot of different steps to get to the final destination. Maybe it's not worth using sql dw in this case and only use sql db?

    Also i guess it's possible going via data lake...but as sql dw doesnt support xml/json loading and my xml is too complex to convert into csv...I'm not sure how to then get it into sql dw

    Any other ideas/advice?

    thanks

    Thursday, March 8, 2018 2:12 PM

All replies

  • anyone?
    Wednesday, March 14, 2018 2:55 PM
  • Hi, and apologies for the delay in getting you a response. Do you have an on-premise SQL Database you could stage the XML data load in to? ADFv2 is very capable of performing this task but to simplify the entire procedure, you may want to load the XML data into an on-premise SQL instance, and then migrate that SQL instance to Azure SQL Data Warehouse. This would be a Source Controlled and Integrated approach per this blog post

    There is more information available in regard to processing and loading XML directly into SQL Server, and is less complex, allowing you to refine and perfect the data load process (Load XML Data).

    The next step would then be to migrate the on-premise SQL data to Azure SQL Data Warehouse. This document covers the available options, including the use of ADFv2.

    Please let me know if the above does not answer your question, or if you need additional information.

    Regards,

    Mike

    Tuesday, March 27, 2018 6:26 PM