Dataset schedule refresh fails intermittently.

[Anonymous]

Hi Team,

I have one Power BI Dataset which is connected to the Azure Database. I have scheduled refresh for five times a day. Out of five times, it works 3 times but fails two times. If I try to refresh manually by Refresh Now option it works completely but by schedule refresh, it is failing. 

I try to troubleshoot by different combinations like time, table of connection but could not get any clue. In Refresh history, I found two types of issues as follows.

Error 1 - 

{"error":{"code":"ModelRefresh_ShortMessage_ProcessingError","pbi.error":{"code":"ModelRefresh_ShortMessage_ProcessingError","parameters":{},"details":[{"code":"Message","detail":{"type":1,"value":"An error happened while reading data from the provider: 'Internal .Net Framework Data Provider error 6.'"}}],"exceptionCulprit":1}}} Table: XXXX.

Error 2 -
{"error":{"code":"ModelRefresh_ShortMessage_ProcessingError","pbi.error":{"code":"ModelRefresh_ShortMessage_ProcessingError","parameters":{},"details":[{"code":"Message","detail":{"type":1,"value":"Microsoft SQL: Server provided routing information, but timeout already expired."}}],"exceptionCulprit":1}}} Table: XXXX.

Please suggest any solution to fix this issue.

Thanks!

[v-chuncz-msft]

@Anonymous 

Take a look at Troubleshoot refresh scenarios and create a support ticket for assistance if necessary.

[Anonymous]

Hi,

I have the same (or similar) situation of intermittent failures of Power BI Pro dataset refresh from an Azure SQL Database.

Failure details: Microsoft SQL: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=35; handshake=24;

Failure details: An error happened while reading data from the provider: 'Internal .NET Framework Data Provider error 6.'

I reviewed the Troubleshoot refresh scenarios page, but did not see any scenarios that fit my situation.  Potentially a timeout issue.

I was wondering if Azure SQL Database is taking a time to "wake" when the Power BI Pro dataset refresh is triggered?  Is there a recommended timeout when refreshing from an Azure SQL Database source?

@ssangekar - were you able resolve your problem?

Thanks!

[Anonymous]

I have the same problem! Any fix yet?

[Anonymous]

Hi piyush,

I implemented a 15 minute CommandTimeout on the dataset and it "seems" to have caused the problem to go away.

Cheers,

Andy

[markharing]

@Anonymous How did you imeplment a 15 minute CommandTimeout?

[Anonymous]

Hi @markharing 

You need to do this from the Power BI Desktop:

- Open the Power BI file

- Open the Data source settings (under the Transform data menu)

- Choose "Change Source"

- Expand the "Advanced options" and you will see a "Command timeout in minute (optional)"

- Set the value here and click Ok

- Save the file and pubilsh it up to the service.

Cheers,

Andy

[DBCoE]

I had this issue too. Here's my solution to fix it.
It is probably too late a response for you given the date of your post but might help others.

I initially thought that the timeout refered to connection timeout, not command timeout.

I connected a dataflow to a serverless SQL server and found that if the connection request is made whilst the sql server is idle, then the startup time for sql is too long and nothing comes back to the requestor within the standard 30 second connection timout. At least, nothing useful comes back.

I added an option to the connection to include a long timeout. e.g. 2 or more minutes

Source = Sql.Database(server, database, [ConnectionTimeout=#duration(0, 0, 2, 0)])

but this didn't fix the issue.

In my case, the request to refresh the dataflow will be triggered by a .net application, so I am ableto check in the application for a failure and retry if I get an Exception.
This doesn't help you though if you ae using another language / process for automating your refreshes.

What I conclude though is, you will have to handle the issue in the process that triggers the refresh, as it doesn't look like you can do anything in Power BI to handle it.

So the main point of this comment, is to say that neither altering the connection timeout in the connection string nor the command timeout in the query, will fix the problem 😞