Synapse Analytics pipeline error: ErrorCode=PolybaseOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error happened when loading data into SQL Data Warehouse. Operation: 'Polybase operation'.,Source=Microsoft.Data

Fabio Carusi 20 Reputation points
2023-03-27T14:32:36.8066667+00:00

Hello there!

I try to copy SQL server tables to a SQL pool using a pipeline of Synapse Analytics and a Copy data activity, but I receive this error message:

Synapse Analytics pipeline error: ErrorCode=PolybaseOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error happened when loading data into SQL Data Warehouse. Operation: 'Polybase operation'.,Source=Microsoft.Data

I use the PolyBase method and enable the staging in the Copy data activity to optimize the performance.

The Bulk insert method completes the run without error, but it's too slow (some tables reach millions of rows).

Thank you for the help!

Regard,

Fabio

Azure SQL Database
Azure Data Lake Storage
Azure Data Lake Storage
An Azure service that provides an enterprise-wide hyper-scale repository for big data analytic workloads and is integrated with Azure Blob Storage.
1,341 questions
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,369 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,542 questions
0 comments
Accepted answer
  1. AnnuKumari-MSFT 30,676 Reputation points Microsoft Employee
    2023-04-05T09:40:04.5433333+00:00

    @Fabio Carusi , Glad to know your issue has been resolved and appreciate your effort for sharing the workaround you followed to mitigate the issue. Since you cannot accept your own answer, I am trying to pen down the solution which worked for you so that accepted answer can help community to get better visibility on solution for this thread. Changing the ADLS dataset to a blob dataset may have helped because PolyBase is optimized for loading data from blob storage. When loading data from ADLS, PolyBase has to go through additional steps to access the data, which can slow down the process. Here are the related documentations for your scenario: Copy and transform data in Azure Blob Storage by using Azure Data Factory or Azure Synapse Analytics Use PolyBase to load data into Azure Synapse Analytics Kindly accept the answer by clicking on Accept answer button. Thanks

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,886 Reputation points MVP
    2023-03-27T15:41:14.3766667+00:00

    Verify there is not type mismatch between source and target. Verify data types are of the correct length and type.

  2. Fabio Carusi 20 Reputation points
    2023-04-04T12:26:38.34+00:00

    I solved the issue to change my storage from Datalake gen.2 to Blob storage. I hope this solution can help those who have the same error. Regards, Fabio

    0 comments