ADF Copy Activity fail when loading Excel file which size is more than 40 MB

Question

I have a requirement where I am using ADF copy activity processing large-size excel files ( includes more than 30 columns, file size around 80-10MBs ) which meets "Out of Memory" error intermittently. I cannot get the solution to this issue, can you please help me with a permanent solution?

Answer 1

For a permanent fix:

1 way would be for you to leverage your own Server as Self hosted IR.

Answer 2

@Vipinkumar Jha welcome to Microsoft Q&A.

I suspect you are using a Self-Hosted Integraton Runtime (SHIR), as I haven't encountered out-of-memory errors on Azure Integration Runtine (Azure IR).

Excel files are treated a little differently than other file types. The entire Excel is loaded into memory at the same time, before any transformation or mapping or copy happens.

If your source is on-prem and sink is in Azure, I recommend you make this 2 step.

1. Do a copy from on-prem to Azure, using Binary dataset for both source and sink. This copies file as-is without opening.
2. Do a second copy Azure -> Azure , with source as Excel, and whatever sink you need. This way you take advantage of the scale of Azure IR for transformation, and avoid out-of-memory errors.