Share via

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

Cossever, Shane 1 Reputation point
Jul 24, 2020, 12:24 AM

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

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,894 questions
0 comments No comments
{count} votes

4 answers

Sort by: Newest
  1. Shane Cossever 1 Reputation point
    Aug 4, 2020, 6:40 PM

    I have tried to set this up, but my output CSV file doesn't have proper columns:

    15560-image.png

    15489-image.png

    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:

    15567-image.png


  2. MartinJaffer-MSFT 26,096 Reputation points
    Jul 30, 2020, 6:51 PM

    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:
    14637-image.png

    My sink dataset, a blob with delimited text format
    14713-image.png

    My Copy activity settings
    14693-xml2csv.gif

    My output:
    14625-image.png

    0 comments No comments

  3. Cossever, Shane 1 Reputation point
    Jul 27, 2020, 9:35 PM

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

    0 comments No comments

  4. MartinJaffer-MSFT 26,096 Reputation points
    Jul 27, 2020, 6:46 PM

    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).

    13926-httpselectformat.gif

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.