locked
Export Data From Azure Synapse Database Table to Blob Storage By Query/SP RRS feed

  • Question

  • Export Data From Azure Synapse Database Table to Blob Storage By Query/SP.

    I have a scenario ..

    There is a SP that runs on Synapse DB and Create some insights and store in a table I want to export that table record as xml file in azure blob container.

    Monday, March 30, 2020 11:45 AM

All replies

  • Hello EathanSpark and thank you for your question.  There are two parts to this.  1) export as XML 2) upload to blob

    Export as XML:

    To export as XML, you can use the BCP utility.  This can look like:

    bcp AdventureWorks.HumanResources.myTeam format nul -f myTeam.Xml -x -n -T

    https://stackoverflow.com/questions/27150672/export-xml-data-using-bcp-command-in-sql-server

    Upload to Blob:

    There is an "Upload to Blob" task as part of the SQL Server Integration Services (SSIS) Feature Pack for Azure.

    If you want your SQL server to connect directly to storage like blob, you must turn on "Allow Trusted Microsoft Services" on the SQL server.  Then you can use the Azure Storage Connection Managerto connect to your blob.   I recommend you use service principal rather than account key, because account key grants admin powers.  With service principal you can use fine grain permissions.

    Monday, March 30, 2020 8:07 PM
  • Just checking to see if the above answer was helpful. If this answers your query, please do consider to click “Mark as Answer” and "Up-Vote" as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.
    Thursday, April 2, 2020 11:32 PM
  • Thanks for your reply... 

    I have two Point ..

    1. Based on synapse table I wan to create Custom nested XML

    2. Without Using SSIS Is there any COPY Commond that can post XML to Blob Container..

    As I have seen that Synapse have provision to copy data from different souces Like sql, blob file, Flat files etc. but it is only availabe for Import Not for Export.

    I am loking for similar method which is availabe for Import.

    Friday, April 3, 2020 6:23 AM
  • Hello EathanSpark

    I have found it is possible, but not supported, to write to blob storage by using the CREATE EXTERNAL TABLE AS SELECT, but there is no XML support for SQL Data Warehouse.  If you can write your entire XML into one column, this method would work by pretending to write a CSV file with only one row/column, and naming the file myfile.xml.

    Other than that, you can use BCP to export in XML file format and then upload with another tool such as AzCopy.

    Friday, April 3, 2020 10:57 PM
  • Since I have not heard back, I will assume the issue resolved.
    Tuesday, April 21, 2020 5:26 PM