We will discuss the error “The gateway is either offline or could not be reached.” when we use an On-premises data gateway to send the data from the cloud to On-Premises.
Problem
Recently we encountered an issue when sending data using an on-premises gateway. Error throws “The gateway is either offline or could not be reached.” but we know there is no issue with the gateway since the previous SQL call was completed successfully. The error information is misleading verifying the gateway.
Root Cause
The root cause is the payload. Logic Apps, Power Apps, and Power Automate support read and write operations through the gateway, which is below the limitation.
-
- Write operations have a 2-MB payload limit.
- Read operations have a 2-MB request limit and an 8-MB compressed data response limit.
- URL for the GET request has a 2048 character limit.
Solution
-
- Split/Break the files into multiple based on data
- Use Power Automate Desktop with RPA plan
- Reduce the payload size (Source to send file size less than 2 MB)
In our case, we can split the file into multiple based on no rows. Here is the payload based on the number of rows. So split into multiple by 500 rows.
|
No of Rows |
Pay Load Size |
|
50 |
400 to 500 KB |
|
100 |
750 KB to 850 KB |
|
500 |
1500 KB to 1700 KB |
Sample
In our example, using a counter and a few variables, we can loop through until it reaches the defined count (500). Here is the sample for the outcome.
Conclusion
We can resolve the payload limitation by splitting the files or RPA. Make sure to buy the user license with RPA.