DM_GWPipeline_Gateway_TimeoutError

[Anonymous]

I am getting lots of these errors on a dataset that used to work. Over the Easter weekend there was an error in one of my SQL based tables which caused duplication and broke my one to many join, this killed the scheduled refreshes I had set on the service. 

I have fixed the error to ensure the join is not broken again, however now that I have re-published the dataset, I am getting this error when it had to load the data back into a separate table from the one that had the error - this table has an Incremental refresh set on it. 

Any help would be appreciated. 

Thanks,

[Anonymous]

I have a similar problem also. I tried this solution for my problem but still it is continuing. I use incremental refresh on my data also. Otherwise I set my connection and commandtimeout time as 6 hours. My data is big and I tried 2 hours on past also. But It didn't useful on later. How can I solve this problem ?

[v-alq-msft]

Hi, @Anonymous 

I'd like to suggest you increase the CommandTimeout and the ConnectionTimeout parameters to two hours to see if it works. The default value for ComandTimeout is ten minutes and the default value for ConnectionTimeout is driver-dependent. You may go to Power Query to modify them. For further information, please refer the document .

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-alq-msft , thanks for your response, I wasn't aware the CommandTimeout default was 10mins. I have been able to update this to 2 hours using the source, however it's not immediately obvious how to also change the ConnectionTimeout.

I have tried setting this in the M Query as below, but I receive the error seen below the opening to the M Query: 

let
Source = Sql.Databases("conncectionstring", [CommandTimeout=#duration(0, 2, 0, 0)] , [ConnectionTimeout=#duration(0, 2, 0, 0)]),
database = Source{[Name="database"]}[Data],

"Expression.Error: 3 arguments were passed to a function which expects between 1 and 2.
Details:
Pattern=
Arguments=[List]"

Are you able to give me a bit of guidance on how I can set this value?

Thanks,

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

You may try the following code in advanced editor. It appears correct.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks Allan, this seems to have worked in testing.