Gateway Pipeline Error [GatewayPipelineErrorCode]=DM_GWPipeline_Gateway_CanceledError

[LasseMr]

Dear community,

I have been struggling with this for a while now.

We are running on a Power BI Embedded Dedicated Capacity and for some of our dataflows we keep getting this PipelineError 

"Gateway_CanceledError".

All dataflows are connecting to SQL sources and standard gateway is running on Windows Server 2019, we moved the gateway from the Windows Server running the SQL database as we thought it might be due to Windows Server being 2008 and thus below minimum requirement, even though it succeeds on other dataflows.

It seems always to be at the exact same second, once a while it manages to run thorugh, but lately it has been very rare, see refresh history for one of the dataflows:

Details on the last fail says;

Sales Line Archive

NA

Failed

########

########

00:00:46.0960

NA

NA

NA

NA

NA

NA

Error: The current operation was canceled because one or more operations failed... RootActivityId = 9ae3c60b-2e5a-459f-b883-f7f8646ec778 Request ID: 4f415a36-7166-2568-9fad-87b2887072d6.

Sales Header Archive

NA

Failed

########

########

00:00:46.0500

NA

NA

NA

NA

NA

NA

Error: Encountered user gateway exception: 'A task was canceled. [GatewayPipelineErrorCode]=DM_GWPipeline_Gateway_CanceledError [ErrorShortName]=GatewayCancellationException/TaskCanceledException[HResult=-2146233029] [ExceptionErrorShortName]=GatewayCancellationException'.. RootActivityId = 9ae3c60b-2e5a-459f-b883-f7f8646ec778.Param1 = A task was canceled. [GatewayPipelineErrorCode]=DM_GWPipeline_Gateway_CanceledError [ErrorShortName]=GatewayCancellationException/TaskCanceledException[HResult=-2146233029] [ExceptionErrorShortName]=GatewayCancellationException Request ID: 4f415a36-7166-2568-9fad-87b2887072d6.

Sales Shipment Line

NA

Failed

########

########

00:00:45.1500

NA

NA

NA

NA

NA

NA

Error: The current operation was canceled because one or more operations failed... RootActivityId = 9ae3c60b-2e5a-459f-b883-f7f8646ec778 Request ID: 4f415a36-7166-2568-9fad-87b2887072d6.

Value Entry

NA

Failed

########

########

00:00:43.3500

NA

NA

NA

NA

NA

NA

Error: Encountered user gateway exception: 'A task was canceled. [GatewayPipelineErrorCode]=DM_GWPipeline_Gateway_CanceledError [ErrorShortName]=GatewayCancellationException/TaskCanceledException[HResult=-2146233029] [ExceptionErrorShortName]=GatewayCancellationException'.. RootActivityId = 9ae3c60b-2e5a-459f-b883-f7f8646ec778.Param1 = A task was canceled. [GatewayPipelineErrorCode]=DM_GWPipeline_Gateway_CanceledError [ErrorShortName]=GatewayCancellationException/TaskCanceledException[HResult=-2146233029] [ExceptionErrorShortName]=GatewayCancellationException Request ID: 4f415a36-7166-2568-9fad-87b2887072d6.

Sales Header

NA

Failed

########

########

00:00:45.9130

NA

NA

NA

NA

NA

NA

Error: The current operation was canceled because one or more operations failed... RootActivityId = 9ae3c60b-2e5a-459f-b883-f7f8646ec778 Request ID: 4f415a36-7166-2568-9fad-87b2887072d6.

Sales Line

NA

Failed

########

########

00:00:45.3960

NA

NA

NA

NA

NA

NA

Error: The current operation was canceled because one or more operations failed... RootActivityId = 9ae3c60b-2e5a-459f-b883-f7f8646ec778 Request ID: 4f415a36-7166-2568-9fad-87b2887072d6.

It is a bit random which query is failing with the "A task was canceled".

I am considering if it might be gateway server-side, but the errors does not mention timeout or similar specifically.

I have also tried desperately to split out the queries in seperate smaller dataflows, changing the steps, changing incremental refresh settings on the ones set for that, tried disabled all logics related to auto-type recognition etc as this could be a load-requiresome task, no luck.

I do also experience an unsual slow preview working in power query only and on saving the dataflow it can take 5-10 minutes for the scheme-check etc to finish before saved.

Other queries runs fine, smallest one, dimensional data, completes in 20-30 seconds, heaviest ones, purchase history with incremental refresh and update on last 10 days, completes in 20-30 minutes.

And on the same dedicated capacity we have 5 other workspaces running on other SQL servers and databases, with no immediate issues or similar errors.

One idea I found looking through the community is increasing the timeout tolerance option, but I also read that standard is 10 minutes, and these cases always fails at exact. 1:14 or 0:44, see other example below:

Any ideas?

[collinq]

Hi @LasseMr ,

I spetn a good chunk of time trying to see if I could find anything helpful.  I really couldn't find anything identical and I circled back to one of the early documents I found - Troubleshoot gateways - Power BI | Microsoft Docs

This talks about looking at the logs, using trace and such but it does suggest opening a ticket.  As does this document Troubleshoot the on-premises data gateway | Microsoft Docs

Please let us know the resolution you discover because I am fully intrigued but don't have a great solution other than those two "generic" articles.

[LasseMr · Accepted solution]

Hi @collinq,

In any case, much appreciated that you spent time looking into the issue!!

I have learned that the problem usually occur when the dataflow needs to reload a full transactional dataset (e.g. added a column or other) or if a new large transactional dataset is added to the dataflow.

Leads me to think that it puts an extra initial load to the server, mashup-wise, which it fails early, looking through documentation and community i concluded the following.

I did two things at the same time;

1. Upgraded server running gateway from 2 CPU to 8 temporary for the first succesful load
2. Changed setting of MashupDSRTestConnectionTimeout from 50sec to 2min30sec in Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config

Following night the scheduled refresh went through with success, 4 hours for the largest one.

The night after the largest took 20 minutes, and been stable at that since - incremental refresh kicked in.

If the solution was 1 or 2, or if I was just "luck" that night, I don't know, and can't reproduce the error now.

In any case, next time, I'll take your lead on enabling advanced log, maybe it will provide better insight.

Thanks anyways.