Copying binary files from REST API in Fabric Data Factory pipeline

[rvp_ordix]

I am currently trying to build a Fabric Data Factory Pipeline that exports a Paginated Report as an Excel File via calling the requisite PowerBI APIs. For some reason, the Copy Activity doesn't seem to be able to handle copying that file to my lakehouse (into the Files folder) and throws an error due to the binary data. 

This is especially annoying since the Azure Data Factory can handle binary data from a REST endpoint, but I sadly need other Fabric components in later steps. 

Using Dataflows or Notebooks directly to call the endpoint where the file is returned is currently unfeasible for varying reasons. 

As a very awkward workaround, I have exported the report as a CSV, caught the plain text via Web Activity and passed it as parameter to a notebook where it is then converted into a DataFrame and then to the required Excel File. 

Is there perhaps a certain configuration that'd allow the Copy Activity to do this? Am I missing another, better way?

Edit:
For clarification, here is a more thorough description of what I attempted.

I used Web Activities to call the Export To File - REST API (with {"format": "XLSX"}, meaning a binary file) and then the Get Export To File Status - REST API to poll the process. Once the export succeeded, I intended to use a Copy Activity with a Web v2 source connection to the Get File Of Export To File - REST API and my Lakehouse in the same workspace as the sink. This failed however.

When configuring the sink connection to write a binary file, it throws the error that the source must be binary as well, for which the Web v2 connection apparently does not count even if it carries a binary file. Setting the sink to write i.e. a delimited text file doesn't work either, since it can't parse the binary data into a csv file. 

[v-veshwara-msft · Accepted solution]

Hi @rvp_ordix ,

Thanks for reaching out to Microsoft Fabric Community.

At this time, the Copy Activity in Fabric Data Factory might not support handling binary content from REST endpoints. When using a Web (or Web v2) source, the service appears to support only JSON responses, either copied as-is or parsed using schema mapping. So even if the REST API returns a binary file such as an Excel document, the response may not be treated as binary, which can result in an error when attempting to write to a binary sink like the Lakehouse Files folder.

Reference: Configure REST in a copy activity 

How to configure Binary format in the data pipeline of Data Factory in Microsoft Fabric - Microsoft ...

The workaround you followed using CSV and a notebook to reconstruct the file might be the most feasible option given the current behavior.

Hope this helps. Please reach out for further assistance.

Thank you.

[rvp_ordix]

I figured as much. It's a bit annoying, but what can you do. Thanks for the confirmation, regardless

[ToddChitt]

Ah, OK. Thank you for the clarification. I understand your intended process, but unfortunately do not have any further input on this. Whish I could help, sorry.

[ToddChitt]

Wait, what? You are CREATING an Excel file by calling the Power BI API on a Paginated Report from inside a Copy Activity?

A Copy Activity should have no problem copying a file to a lakehouse. What is the Source? A WEB connection? Can you explain in further detail the layout and tasks?

[rvp_ordix]

I used Web Activities to call the Export To File - REST API (with "format": "XLSX", meaning a binary file) and then the Get Export To File Status - REST API. Once the export succeeded, I intended to use a Copy Activity with a Web v2 source connection to the Get File Of Export To File - REST API and my Lakehouse in the same workspace as the sink. This failed however.

When configuring the sink connection to write a binary file, it throws the error that the source must be binary as well, for which the Web v2 connection apparently does not count even if it carries a binary file. Setting the sink to write i.e. a delimited text file fails as well, since it can't parse the binary data into a csv file.