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. 

[Tarun_khudiya]

• 1. Create the Source Linked Service
• First, you must establish the connection to your API.
• In your Fabric workspace, go to Data Factory -> New -> Linked Service.
• Select REST.
• Enter the Base URL of your API.
• Configure the Authentication (Anonymous, Basic, or OAuth2, depending on your API requirements).
• Test the connection to ensure it is successful.
• 2. Create the Source Dataset
• This is the most critical step for binary files.
• Create a new dataset for your source and select REST.
• In the Format selection, choose Binary.
• Why? Standard REST datasets usually expect JSON; choosing "Binary" tells Fabric to treat the raw response body as a file rather than trying to parse it as structured text.
• Do not specify a complex schema. Since it is binary data, the file structure is handled directly by the copy activity.
• 3. Create the Sink Dataset
• You need to define where the file will land.
• Create a new dataset for your destination (e.g., Azure Data Lake Storage Gen2 or OneLake).
• Choose Binary as the file format here as well.
• Define the file path and name (you can use parameters if the file name changes dynamically).
• 4. Configure the Copy Activity
• Now, assemble the pipeline:
• Drag the Copy Data activity onto your pipeline canvas.
• Source Tab:
• Select the Binary REST dataset you created in Step 2.
• Set the Request Method (usually GET or POST).
• If you need to pass dynamic headers or parameters, you can add them in the Additional headers or Query parameters section.
• Sink Tab:
• Select the Binary Sink dataset you created in Step 3.
• Settings Tab:
• Ensure Copy behavior is set to "Preserve hierarchy" or "Flatten hierarchy" depending on your needs.
• If the file size is large, ensure the Degree of copy parallelism is set correctly (default is usually fine, but you can adjust it for performance).
• Pro-Tips for Success
• Handle Headers: If your API requires a specific Accept header to return the file (e.g., application/octet-stream or application/pdf), ensure you add this in the Additional headers section of the Source configuration.
• Dynamic File Names: If you need to generate unique filenames for each run, use a Pipeline Expression in the Sink dataset file path (e.g., concat('file_', utcnow(), '.dat')).
• Pagination: If your API returns data in multiple chunks or pages that need to be merged into one file, check if the API supports standard pagination links. If it returns multiple separate files, the "Binary" format will generally treat each response as a separate object unless handled specifically by an Append operation.

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