Dataflow - Status Failed - Timeout Error

[Anonymous]

Hi all,

I'm hoping anyone have a good idea on the issue I'm currently facing with my Dataflow

My dataflow is currently loading data from 2 views (let's call it Query 1 and Query 2) where the source is an Azure SQL DB (Standard 100DTU plan elastic pool)

Query 1 is the one which keep having errors/status failed below. Executing it from SSMS directly only take 2-3 mins and consist only 50k records

Error: Data Source Error : Microsoft SQL: Error retrieving data from <my_sql_db_name>.  The underlying error message received was: 'One or more errors occurred. Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.'...

No issues with Query 2 and status completed. This query successfully returns 500k records

This is my ingestion dataflow so no enhancements done on the dataflow other than the incremental refresh hence I'm wondering what went wrong. My dataflow is also on a premium dedicated capacity. I monitored the eDTU's and session on my SQL server and it only hits a maximum of 70% and 20% respectively

Appreciate if anyone can shed some light to the problem or ways to navigate in finding the culprit

Many thanks

[v-xuding-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly share your solutions. More people who encounter the same issue will benefit from here. 

[Anonymous]

Hi XD,

Thank. It seems to solve my issue but at the same time, I've also scaled up the source (Azure SQL) and broken down my dataflow into multiple dataflows and hold less entities

I'm still monitoring but I'm convinced the setting did help

Cheers

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Please add the parameter of timeout into PQ query to have a try.

[Query="< query is here >", CommandTimeout=#duration(0, 1, 0, 0)]

A user encounter the issue while editing dataflow without doing any changes.  And his problem hasn't fixed for now. I don't know if your situation is same as his.  Please try the code above firstly. If it still don't work,  please create a support ticket to get the dedicated support.

How to create a support ticket in Power BI  

[hagbergHCC]

For those that are having the same issue of a dataflow incremental refresh failing due to a timeout, adding this parameter also solved it for me. However, it was unclear exactly how to accomplish this with the dataflow as the query syntax was not what I could see in Power Query. After further research I found that the specifics are to add the CommandTimeout to the Source line at the top of the M code in the Advanced editor for the dataflow entity that is being set up for incremental refresh:

Source = Sql.Database("server", "database", [CommandTimeout=#duration(0, 1, 0, 0)])

The format of the duration is (day, hour, minute, second) so the above line sets the CommandTimeout to 1 hour.

Here are the details of my issue and results:

• Dataflow from Azure SQL database pulling a single table (~43.5M rows) for data ingestion only (i.e., no transformations)
• non-incremental refresh: 53 minutes
• incremental refresh for 6 years total and 7-day incremental refresh
• many failed incremental refreshes: ~10 minutes
• added index to SQL table on date field: failed refreshes after ~15 minutes (held out slightly longer)
• Added CommandTimeout to Source line: initial refresh 1 hr 42 min to pull in and set up all the partitions
• Successive incremental refreshes: ~20 seconds -- even better than I had hoped for with incremental refresh on this entity!