Copy Activity Fails with "Incorrect syntax near 'INSERT BULK'" when using SQL Server Linked Service

[sshetty014]

Hi everyone,

I ran into an issue while building a pipeline to copy data from SAP S/4HANA into Microsoft Fabric Warehouse and wanted to check if anyone has faced something similar.

Current setup:

• Data is initially extracted from SAP S/4HANA and landed in Lakehouse Files as Parquet files

• I’m now trying to move the data from Lakehouse Files to Fabric Warehouse using a Copy activity

Configuration:

• Source: Lakehouse Parquet file

• Sink: Fabric Warehouse, connected via SQL Server linked service (using the SQL analytics endpoint)

• Write behavior: Insert

• Bulk insert table lock: No

However, the pipeline fails when writing to the Warehouse.

I came across a blog mentioning that BULK INSERT is supported in Fabric Warehouse, so I wanted to understand whether this approach should actually work.
https://learn.microsoft.com/en-gb/sql/t-sql/statements/bulk-insert-transact-sql?view=fabric

The reason I’m using the SQL Server connector instead of the native Fabric Warehouse connector is because my broader use case requires a dynamic pipeline with multiple possible destinations, one of which is a standard SQL Server connection. My thought was that using the same connector type would make it easier to dynamically switch connections and reuse the same pipeline logic.

Has anyone implemented something similar, or is using the SQL analytics endpoint as a sink for Fabric Warehouse not a supported pattern in this scenario?

Any guidance would be appreciated.

[v-echaithra · Accepted solution]

Hi @sshetty014 ,

Thank you for reaching out to Microsoft Community.

Although Fabric Warehouse supports the `BULK INSERT` T-SQL statement, using the SQL Server linked service against the Warehouse SQL analytics endpoint is not a fully supported sink pattern for Copy Activity bulk loading.

The SQL Server connector internally uses SQL server specific bulk insert operations (`INSERT BULK`) which are not fully compatible with the Fabric Warehouse endpoint, resulting in the syntax error.

For Fabric Warehouse destinations, the recommended approaches are:

* Use the native Fabric Warehouse connector in Copy Activity
* Use `COPY INTO` from OneLake/Lakehouse files
* Use Dataflow Gen2 or Notebook-based loading patterns

For dynamic multi destination pipelines, the recommended design is usually to branch logic based on sink type rather than reusing the SQL Server connector for Fabric Warehouse targets.

Thanks.