Forum Discussion

HannaMelashkina's avatar
HannaMelashkina
New Member
6 months ago

[Azure Data Factory] Data Flow Sink is extremely slow

I'm copying about 800 items from SAP to Azure Fabric. I'm running pipeline with data flow in Azure Data Factory (ADF). We are using Self-hosted integration runtime. The Data Flow is not complex, it just copies data and adds an additional column. There are no complex transformations.

 

However, the Sink activity takes around 10 minutes!

 

Sink configuration:

  • Sink type: dataset
  • Dataset: Fabric warehouse
  • Allow schema drift: true
  • Allow insert: true
  • Batch size: 100
  • Enable staging: false (I cannot turn it on; otherwise, I get an error in my pipeline: "Fabric DW supports staging only via copy command - EXE-0001, Dataflow cannot be analyzed as a graph.")

I also tried using Copy activity as suggested here, but it keeps failing with a BULK insert error:

Failure happened on 'Sink' side. ErrorCode=SqlOperationFailed, 'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException, Message=A database operation failed. Please search error to get more details., Source=Microsoft.DataTransfer.Connectors.MSSQL, ''Type=Microsoft.Data.SqlClient.SqlException, Message=Incorrect syntax near 'INSERT BULK'., Source=Framework Microsoft SqlClient Data Provider,'

 

Some other observations:

  • Sink processing time: 10m 33s
  • Stage time: 7m 36s
  • Table operations SQL duration: 2m 30s

I also checked in the warehouse's long_running_queries and saw that INSERT was called 840 times, with each insert taking around 450ms. That means 800 * 450ms = 360,000ms (6 minutes)!

 

My Question: Why is ADF inserting items one by one and ignoring the batch size of 100? Since I cannot enable Staging without errors and BULK insert fails, what is the correct configuration to load data from SAP to Fabric efficiently?

azure data factory
azure fabric
SAP

5 Replies

  • DanieleUgoCopp's avatar
    DanieleUgoCopp
    Super User
    6 months ago

    Hello,
    I’m not fully sure, but from what you describe it looks like ADF Data Flow isn’t really batching the inserts when the sink is a Fabric Warehouse.
    Fabric DW is still limited, Data Flow generate individual INSERT statements, I don't think it is a misconfiguration on your side.

    What I would try is avoiding Data Flow for this and using Copy activity with a supported write method, or landing the data first in ADLS or OneLake and then loading it into Fabric using a native Fabric load or SQL COPY.

    Best regards,
    Daniele

  • MohitsinghMS's avatar
    MohitsinghMS
    Resolver II
    6 months ago
    Spoiler
     

    Use Copy Activity with Staging Enabled

    For a simple "Copy + Add Column" task, Data Flow is overkill and expensive. The Copy Activity is the correct tool, but you must configure it correctly to avoid the INSERT BULK error.

    The Incorrect syntax near 'INSERT BULK' error occurred because you tried to use Copy Activity without Staging. Fabric does not fully support the legacy TDS INSERT BULK stream used by standard SQL drivers; it demands the COPY command for performance.

    Steps to fix:

    1. Switch to Copy Activity:

    • Source: Your SAP Dataset.

    • Sink: Your Fabric Warehouse Dataset.

    • Add Column: You can add the "Additional Column" directly in the Source tab of the Copy Activity (under "Additional columns") without needing a Data Flow.

    2. Enable Staging (Crucial Step):

    • Go to the Settings tab of the Copy Activity.

    • Check Enable staging.

    • Staging account linked service: Select an Azure Data Lake Storage Gen2 (ADLS Gen2) linked service. (You must have an ADLS Gen2 account to act as the temporary buffer).

    • Storage Path: Pick a folder (e.g., adf-staging).

    3. Sink Settings:

    • Ensure the Copy method is set to COPY Command (this usually becomes the default or only option when Staging is enabled for Fabric/Synapse connectors).

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    6 months ago

    Thankyou, DanieleUgoCopp and MohitsinghMS for your responses.

    Hi HannaMelashkina,

    Thank you for sharing the approach, which will benefit other members of the community.

    If you require Copy activity support for SAP CDC, we kindly request you to post an idea using the link: Ideas · Community. The product team regularly reviews this platform for popular feature requests. Should a considerable number of users endorse an idea, Microsoft will prioritise its incorporation in a future release.

    Thank you.




  • HannaMelashkina's avatar
    HannaMelashkina
    New Member
    6 months ago

    SAP CDC Dataset is not supported by copy activity docs.

    What I could do: in dataflow instead of sink to warehouse, we sink in ADLS Gen2 as Parquet files. And in Copy activity I pass ADLS Gen2 as Source and Warehouse as destination. This way copy is working faster. In my opinion that's a big mistake from MS not to support Copy activity for SAP CDC.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    6 months ago

    Hi HannaMelashkina,

    Could you kindly confirm whether you have submitted this as an idea in the Ideas Community? If so, please share the link here, as it would be helpful for other community members who may have similar feedback.

    If you have any further queries, please feel free to reach out to the Microsoft community.
    Thank you.