1

I have a flow where the following is taking place

  1. Query an SQL Database and retrieve a dataset with 21780 records
  2. Parse the ResultSets.Table records over to JSON
  3. Using HTTP connector perform POST to an Excel worksheet enter image description here
  4. This has worked in the past but recently we went over 20K rows now the flow fails with a Redirect error. enter image description here
  5. The raw outputs are below
    {
        "statusCode": 302,
        "headers": {
            "Strict-Transport-Security": "max-age=31536000",
            "request-id": "8b125471-c9fd-4770-b2e4-",
            "client-request-id": "8b125471-c9fd-4770-b2e4-",
            "x-ms-ags-diagnostic": "{\"ServerInfo\":{\"DataCenter\":\"East US\",\"Slice\":\"E\",\"Ring\":\"4\",\"ScaleUnit\":\"001\",\"RoleInstance\":\"BY1PEPF00002543\"}}",
            "Cache-Control": "private",
            "Date": "Wed, 16 Aug 2023 23:41:41 GMT",
            "Location": "/error/error.html?aspxerrorpath=/x/_layouts/xlrestinternal.aspx",
            "Content-Length": "0"
        }
    }
  1. If we update the SQL in step one with SELECT TOP 20000 the HTTP POST does not fail.
  2. We tried Enabling Pagination. Set a Threshold value, Allow chunking with no success.
  3. We also tried the Apply to each with an Add a row into table - which works but takes Hours to process the data; not to mention the cost we are incurring per execution. enter image description here

I suppose HTTP is the wrong connector to get this data over to an Excel document - What connector should we use to move such datasets over?

Geovani Martinez
  • 2,053
  • 2
  • 27
  • 32
  • You can always manually page your data and load it in that way. Use `skip` and `take` over your dataset and load 10,000 records at a time or something. Make sense? You'd need to just keep looping until the `skip`/`take` call didn't return any results. – Skin Aug 17 '23 at 01:38
  • Like this concept ... https://stackoverflow.com/questions/70670651/power-automate-flow-process-json-output-in-50-parallel-branches/70674184#70674184 – Skin Aug 17 '23 at 01:56

0 Answers0