Azure Data Factory Dataverse to SQL - Timeout failures when creating table.

Jordan Fulford 1 Reputation point

I'm getting repeated and consistent timeout failures during the DataverseToSQL pipeline step of the following guide. The process fails due to a timeout when attempting to create a table on the SQL server. What can I do to resolve this?

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-pipelines?tabs=synapse-analytics

👁 User's image

0 comments No comments
Sign in to comment

2 answers

  1. Pilladi Padma Sai Manisha 10,190 Reputation points • Microsoft External Staff • Moderator

    Hi @Jordan Fulford
    Based on the screenshot, the timeout occurs in the CreateTable (Script) activity, not during Dataverse extraction or SQL connectivity. Multiple CreateTable activities completed successfully before and after the failure, which suggests the linked service and firewall configuration are working correctly.

    The most likely causes are SQL-side blocking, resource contention, or a schema/object conflict affecting a specific table creation operation. I recommend opening the failed CreateTable activity and reviewing the Output/Error Details, identifying the Dataverse entity being processed, and checking the target SQL database for blocking sessions, deadlocks, and resource utilization during the failure window.

    Could you share the exact error message from the failed CreateTable activity and confirm the target database type (Azure SQL Database, SQL Managed Instance, or Synapse Dedicated SQL Pool)?

    1. Pilladi Padma Sai Manisha 10,190 Reputation points • Microsoft External Staff • Moderator

      Hi @Jordan Fulford
      Following up to see if the below answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

    Sign in to comment

  2. AI answer

    Timeouts when Azure Data Factory creates a table in SQL during a Dataverse-to-SQL copy are typically caused by SQL-side issues or long-running operations. Use the following steps to mitigate and troubleshoot:

    1. Increase SQL timeouts in the copy activity
      • In the pipeline activity that writes to SQL (sink), increase:
        • Query timeout for source-side queries if applicable.
        • Write batch timeout for the SQL sink.
      • This directly addresses timeouts described under SqlOperationFailed when the error message contains Execution Timeout Expired.
    2. Check for SQL blocking and long-running operations
      • Timeouts during table creation or initial write can be caused by blocking or long-running transactions on the SQL database.
      • Investigate SQL blocking and concurrency:
        • Review active sessions and locks on the target database.
        • Look for other operations that might be creating or altering the same table or schema.
      • For Dataverse-related SQL timeouts, blocking and cascading operations are common causes. Ensure that no other processes are locking the same objects while the pipeline runs.
    3. Validate SQL database health and configuration
      • Confirm that the SQL database is responsive and not under resource pressure.
      • If the error is surfaced as SqlOperationFailed with SqlException, ensure the database is at a supported compatibility level and review the detailed SQL error to identify specific issues.
    4. Retry and handle transient failures
      • Timeouts can be transient. Configure the pipeline to retry on failure where appropriate.
      • For connection-related timeouts (for example, SqlOpenConnectionTimeout), increase the connection timeout in the connection string and retry, as recommended for transient SQL database failures.
    5. Validate schema and mappings when auto-creating tables
      • If the pipeline is auto-creating the SQL table, ensure that:
        • Column types from Dataverse can be mapped to supported SQL types.
        • There are no unsupported or mismatched data types that could cause failures during table creation or initial write.
      • If necessary, pre-create the target table manually with appropriate schema and point the sink to this table instead of relying on auto-create.
    6. Re-run after resolving SQL-side issues
      • After increasing timeouts, resolving blocking, and validating schema, re-run the DataverseToSQL step.
      • If failures persist and the error message includes other specific SQL error codes or messages (for example, PdwManagedToNativeInteropException or invalid column names), adjust column sizes, mappings, or structure accordingly.

    These steps align with documented guidance for handling SQL timeouts and SqlOperationFailed errors in Azure Data Factory/Synapse pipelines and for SQL timeouts in Dataverse-related operations.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.
Sign in to answer

Your answer