[DataSource.Error] Microsoft SQL: "Execution Timeout Expired." for Dynamics365 Import

[Flawn]

Hello All,

I have imported a series of tables from a Dynamics365 database. 11 of the tables I have imported work fine, but one of the tables - not even the largest one - always returns the following error when I attempt to 'apply' the import from the query page:

"Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: "Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.""

I have found answers to this issue on the forum and on the microsoft support page for when the import is being run directly from an SQL server - usually the answer appears to be to manually adjust the timeout period. In this case, the option is not available - or at least I cannot find it in the areas proposed by those support pages or forum solutions. I suspect it is because the data is coming directly from the Dynamics365 server, rather than from an SQL source.

If anyone has any solutions to this issue I would greatly appreciate it: I have tried clearing the cache, re-establishing the data permissions/authenications, and running the import from a server box.

Thanks again!

[SaiNikhithaM]

Hi Flawn, I am facing the same issue. Did you find a solution for this issue?

[Flawn]

I did, but it wasn't a very good one.

We were forced to simplify and rebuild the source table within the datalake we were exporting from - once its complexity was sufficiently reduced we were able to get it to export. It took a while and diminished the reporting power somewhat but literally nothing else was working, and I had asked a few times across various areas at that point and recieved no solutions - so it was either a less-good report or no report at all.

Sorry I don't have a better answer for you

[Anonymous · Accepted solution]

Hi @Flawn 

As you mentioned, importing data from Dynamics365 for SQL Server timeout adjustments may not be applied directly.

You can perform the following to try to resolve the issue:

Before importing, try to optimize the queries used to get data from Dynamics365.

This may include selecting only necessary columns, filtering rows in the source file, or simplifying joins.

The goal is to reduce the amount of data transferred and processed.

While the direct option to adjust the timeout for a Dynamics365 source might not be available, you can adjust the timeout settings in Power BI Desktop.

You can go to power query and click on "Data source settings" under the "Home" tab.

Select "Change source...". and increase the timeout duration in "advanced options".

Sometimes problems can be related to network latency or server performance.

Ensure that the network connection is stable and check if the Dynamics 365 server is experiencing any performance issues during the import process.

Regards,

Nono Chen

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

[Flawn]

Firstly, thank you for the reply and the information - i'm running through all of these suggestions systematically.

But I have run into an immediate issue:

I am unable to select the 'change source' option.

Any advice to fix that?
Warm Regards,
Sam