Reading File size issue greater than 4 MB

When I use a lookup activity to read a csv file which is 6MB size file, the pipeline fails throwing error the size of lookup activity results exceeds the limitation 4194304. Could you please suggest how to overcome this issue.

Mark Kromer MSFT 1,146 Reputation points

2021-06-14T22:58:50.777+00:00

What did you want to do with that data from the lookup? A use case that is reading data from a file for processing would fall inside of a data flow use case. Did you try moving your logic into a data flow to avoid this limitation?

1 answer

KranthiPakala-MSFT 46,462 Reputation points Microsoft Employee

2021-06-14T19:01:40.147+00:00
Hi @Ramsekar Chandrasegara sastry ,

Welcome to Microsoft Q&A forum and thanks for the ask.

The issue you are seeing is because of the limitation by design.
Here are lookup activity limitations:

The Lookup activity can return up to 5000 rows; if the result set contains more records, the first 5000 rows will be returned.

The Lookup activity output supports up to 4 MB in size, activity will fail if the size exceeds the limit.

The longest duration for Lookup activity before timeout is 24 hours.

When you use query or stored procedure to lookup data, make sure to return one and exact one result set. Otherwise, Lookup activity fails.

As a workaround you could try breaking the source file to smaller files (<4 MB) which would help avoid this issue.

Hope this helps. Do let us know how it goes.

----------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.
Please sign in to rate this answer.
KranthiPakala-MSFT 46,462 Reputation points Microsoft Employee

2021-06-15T19:27:53.27+00:00

Hi @Ramsekar Chandrasegara sastry ,

Just checking in to see if any of the above suggestion was helpful. If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

Ramsekar Chandrasegara sastry 1 Reputation point

2021-06-16T09:27:34.193+00:00

Thanks for your response KranthiPakala-MSFT
I have used a CSV source file with 2052 records and size 4.9Mb and used lookup activity in the pipeline to read the number of records in a file and used the expression @greater(activity('Get DataFile RecordCount').output.count,1) in the IF condition activity to check the record count , but I get the Error Code 2100 from lookup activity.

"ErrorCode=UserErrorComputePluginFailure,'Type=Microsoft.Data.Transfer.Common.Shared.HybridDeliveryException,Message =The size of lookup activity result exceeds the limitation 4194304".

Is there any other alternate way to do read the count of file other than lookup?

KranthiPakala-MSFT 46,462 Reputation points Microsoft Employee

2021-06-18T06:50:58.12+00:00

Hi @Ramsekar Chandrasegara sastry ,

Thanks for your response. Could you please check this thread and see if you can implement a similar workaround to avoid the issue - How to process more than 5000 records in Lookup Activity?.

Another workaround is explore Mapping data flow as per Mark kromer's suggestion.

Do let us know it goes.

KranthiPakala-MSFT 46,462 Reputation points Microsoft Employee

2021-06-21T18:32:41.557+00:00

Hi @Ramsekar Chandrasegara sastry ,

We still have not heard back from you. Just wanted to check if the below suggestion was helpful? If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

Ramsekar Chandrasegara sastry 1 Reputation point

2021-06-21T19:13:45.583+00:00

Hi KranthiPakala-MSFT,
Thanks for getting back to me. I went thru the thread that you shared to me" How to process more than 5000 records in Lookup Activity?." But that doesn't help as my inout is file and i would not able to split the input file as I would not know the number of records. My ask here is...Instead of look up activity (as we know it has limitations) what is the alternate way to read the record count of file.(I don't want to got for Data flow activity). Please note I might also get empty file with only headers (this should also be processed with 0 records)

Mark Kromer MSFT 1,146 Reputation points

2021-06-21T20:26:37.347+00:00

Data Flows can process n-number of rows. Pipelines are for control flow, not data flow operations like this. Why are you not using data flows?

KranthiPakala-MSFT 46,462 Reputation points Microsoft Employee

2021-06-24T04:56:56.057+00:00

Hi @Ramsekar Chandrasegara sastry ,

Thanks for your response. As Mark called out Mapping Data flow is the best alternate within ADF at the moment. There are other activities like Custom activity and Azure Function Activity within ADF which can be used to move data to/from a data store that Data Factory does not support, or to transform/process data in a way that isn't supported by Data Factory.

Hope this info helps.

Ramsekar Chandrasegara sastry 1 Reputation point

2021-06-24T09:51:05.463+00:00

Hi Mark, Dataflow generally takes 4 mins to start the cluster and moreover for just reading the number of records of file, i thought there wud be any other alternate approach to get the count instead of Data flow.

KranthiPakala-MSFT 46,462 Reputation points Microsoft Employee

2021-06-24T17:44:39.91+00:00

Hi @Ramsekar Chandrasegara sastry ,

Thanks for your response. As mentioned in my previous comment, you may give a try writing your own code to get the row count and using it in either Customer Activity or Azure Function activity of ADF pipelines.

If you have feedback for a specific feature request, I would recommend you to please submit your idea in ADF user voice forum: https://feedback.azure.com/forums/270578-data-factory. All the feedback shared in this forum are actively monitored and reviewed by ADF engineering teams. Also please do share the feedback link here once it is posted so that others with similar idea can upvote and comment on your feedback.

Do let us know if you have further query.

Thank you

KranthiPakala-MSFT 46,462 Reputation points Microsoft Employee

2021-06-24T18:00:38.423+00:00

One other alternate I could think of is to load data first in to Azure Sql table if it is okay for you and then do lookup.

Copy activity --> data copies from csv to table

Lookup activity --> executes query to get count from table

But I don't think it is a good approach as you have to load entire file in SQL table only to get row count and then delete data from that table. But it is another option other than writing your own code and using Custom or azure function activities.

As it is related to a product limitation, I would highly recommend you to please share your feedback in user voice feedback channel shared in my previous response.

Hope this helps

KranthiPakala-MSFT 46,462 Reputation points Microsoft Employee

2021-06-25T23:02:36.583+00:00

Hi @Ramsekar Chandrasegara sastry ,

Just checking in to see if the above information was helpful. And, if you have any further query do let us know.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Reading File size issue greater than 4 MB

1 answer

Your answer