How to convert binary data retrieved via HTTP into a parsable type

Question

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

Answer 1

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.

Answer 2

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,'",

Answer 3

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:

Answer 4

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: