REST API Fails intermittently through simple ADF copy pipeline

Question

Hi Experts , I have got one of the rest API which returns 471600 records and takes around 1.5 mins to get the data . I created a ADF copy pipeline to read the data from API and insert in the Azure table . I scheduled the pipeline to run every 4 mins . The pipeline works intermittently , it will be success for few run and would fail for few . Which makes the data pull very unreliable .

Error is very generic as below
Operation on target Copy_cvc failed: Failure happened on 'Source' side. ErrorCode=JsonInvalidDataFormat,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error occurred when deserializing source JSON file ''. Check if the data is in valid JSON object format.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=Newtonsoft.Json.JsonReaderException,Message=Unterminated string. Expected delimiter: "

I know that there is no issue with the data and not sure if it pulled the data half way and hence it reports the unterminated string as on the other run it works . The data from the API is static and does not change frequently .
One of the thing i have noticed is the that in the sink setting is make the Write batch size as the higher number around 100000 then it does not work at all then i reduced to 5000 so it works intermittently .

Can anybody please help on this issue below is the screen shot of the pipleline

Any help is appreciated .
Thanks

Answer 1

Hi , thanks for your quick response . I have tried to use the api on Postman site but as the response data is big and doesnt come back in 30 sec hence it does not give response back , but it does work intermittently from the pipeline in around 1min and 40 sec to get the data . My issue really started from the sql server integration -ssis package where this is the same behavior where i am using the http client dll from .net to get the response back , there also it was failing intermittently and on few server it does not work at all so i decided to try on Azure pipelines but here again i am seeing the same issue so i have almost hit like and impasse now where the response is not reliable at all .
Can you please help if you have seen these kind of issues before and what best can be done in these kind of scenarios . I could try the retyr option if you could guide me please .

Thanks
Abhishek

Answer 2

hi @Bhargava-MSFT ,

thanks please can you please me the link to create a support issue , also not sure if we will be able to share the information as the API shows confidential information .

Thanks
Abhishek