This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(83.2, 57.99999999999999%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECS%3C/text%3E%3C/svg%3E)
I am connecting to an HTTP source (SOAP API) via POST, and retrieving Binary data. How can I get this data into a parsable format that can be used to copy to a data warehouse? The goal is to use this data for PowerBI reports.
Thank you
An Azure service for ingesting, preparing, and transforming data at scale.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(60.8, 46%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESC%3C/text%3E%3C/svg%3E)
I have tried to set this up, but my output CSV file doesn't have proper columns:
The value in this mapping is pushing the data to the Error column, and then making additional columns... I cannot remove the delimiter option in this kind of XML to CSV activity:
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 70%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMM%3C/text%3E%3C/svg%3E)
I see, I assume this is caused by the commas , in the JSON-like section of the value column.
Ordinarily I'd suggest using the "quote all text" feature. Since the data already contains quotes, the benefit is dubious.
You original ask was to make this into a "parseable type". The data warehouse has many options. Would you be open to using something other than csv? Tab delimited (.tsv) would be a better option. To accomplish this, all you would need to is change the delimiter from comma to tab.
Please let us know if you still need assistance on this query, or mark something as answered.
Yes I have switched to JSON and have been having an easier time mapping data and avoiding these kinds of issues.
You were on the right track. The source should be HTTP->XML. The Sink should be Blob->CSV or SQL as appropriate. The conversion happens in the mapping step of the Copy Activity.
In the below, I show details how I accomplished your ask, @Cossever, Shane .
My source data:
My sink dataset, a blob with delimited text format
My Copy activity settings
My output:
I am using the XML choice. I run the HTTP connector, the POST is sent, and XML is retrieved. How can I now save/sink this XML data into something that can be written into a database and/or CSV file?
@MartinJaffer-MSFT When I tried to save as CSV in Source, I get the following error:
ErrorCode=SchemaMappingFailedInHierarchicalToTabularStage,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to process hierarchical to tabular stage, error message: One or more errors occurred.,Source=Microsoft.DataTransfer.ClientLibrary,'",
Hello @CosseverShane-0919 and thank you for your question. Could you please help me understand the situation a little more?
If you are using the HTTP connector, you get a choice of what sort of data format to expect. (See below video).
Binary format is used for data which you either do not want to parse, or data which cannot be parsed (such as images and executables).
All the other formats are parsable.
I feel like I may have misunderstood your ask. If so could you please clarify and give examples?
If this was what you were looking for, please mark as answered.