Can't Use CopyData Activity in Fabric Pipeline Because Source Has Over 5000 Files

[Kawby]

I'm trying to develop a Fabric Pipeline that will take JSON files from our Azure Blob Storage and move them to a Lakehouse in our Fabric environment. This Blob Storage repo, however, has over 5,000 files. I understand there's a limit to the API the CopyData activity hits, so this won't be viable. I've tried creating a Lookup + Filter + ForEach pipeline to try and limit the files pulled over, but I'm still hitting that limit in the Lookup.

Has anyone figured out a way to do this, or am I going to have to do this in a Notebook?

[v-venuppu]

Hi @Kawby ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @AsgerLB @tayloramy for the prompt response.

I wanted to check whether did you had a chance to try the approach mentioned by you and able to resolve the issue..?If not have you tried the approach suggested by @AsgerLB..?Please let us know if you need any further assistance.

[tayloramy]

Hi @Kawby, 

I've not run into this before, do you have a link that describes this limitation? 

What comes to mind first is to use a Lookup activity to get the file you want, and then pass the full path to the copy activity. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution. 

[Kawby]

There are plenty of sources out there that state the 5,000 record or 4 MB limit from the Rest API that ADF/Fabric Pipelines use, but I can't find any official documentation; however, I am definitely running into this issue.

As I stated in the original post, I've tried a LookUp activity paired with a Filter, but it's not making it past the LookUp activity due to the Azure Blob Storage container having more than 5,000 files to look through. It's not a CU problem either.

[AsgerLB]

Hi @Kawby 

I believe you are reffering to the LookUp activity limmitation - this should not be an issue for your copy data activity. If a LookUp is nessecary, you can try to design a two (or more)-level pipeline where the outer pipeline iterates over an inner pipeline, which retrieves data that doesn't exceed the maximum rows or size and or using pagination if possible.

Br

Asger

[Kawby · Accepted solution]

Thanks @AsgerLB, that's a good idea. I might give it a try after I try something I thought of:

Creating a control table to store a date from when the files are moved and pull a date value that can be parameterized into the CopyData/Lookup activity. This will limit the amount of files pulled anyway, so maybe the Lookup would be irrelevant at that point.

[AsgerLB]

Hi @Kawby ,
While I haven't heard about the specifc limitation you mention - if its a matter of sending too many requests within a too short timewindow (i.e. throtteling) then you can try and use the wait activity to pause between runs. 

Alternatively, if it is not strictly nessecary to use pipeliens for copying in the data, then you could utilize the Lakehouse shortcut for Blob storage for getting the json files available in Fabric - Create an Azure Blob Storage shortcut - Microsoft Fabric | Microsoft Learn

Br
Asger

[Kawby]

For my purposes, the LookUp activity and CopyData activity seems to have limitations when returning information. A maxiumum of 5,000 records or 4 MB can be returned at once. Due to this, the Azure Blob Storage I'm connecting to that has about 6,000 files won't return information and error's out. I'm attempting to do a lookup on the container, then filter out about 500 files at a time to be processed in a CopyData activity via a ForEach activity. It's too much for the LookUp, and I'm not sure how to limit prior to the LookUp.

I've thought about Shortcutting to the Azure Blob Storage container via the Lakehouse, but we want to "own" the files.