SqlBulkCopy.WriteToServer is not working with Fabric Data Warehouse (Azure)

[geiraron]

When I try to write a DataTable to the user with the WriteToServer, I receive the the following exception:

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): INSERT is not a supported statement type.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.SqlBulkCopy.RunParser(BulkCopySimpleResultSet bulkCopyHandler)
   at Microsoft.Data.SqlClient.SqlBulkCopy.SubmitUpdateBulkCommand(String TDSCommand)
   at Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)
   at AzureSQLMFA.Program.Main() in C:\Developer\CSharp\2022\Budsjettprosjekter\AzureSQLMFA\AzureSQLMFA\Program.cs:line 246
ClientConnectionId:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Error Number:22424,State:0,Class:16
ClientConnectionId before routing:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Routing Destination:XXXX.pbidedicated.windows.net\XXXXXXXXXXXXXXXX-dw,1433 

I am using the following code:
bulkCopy.DestinationTableName = "Personnel";
bulkCopy.WriteToServer(personnelDataTable);

The same code is working as expected when I use an on-premise MS SQL Server 2022:

I am able to insert data to the warehouse in Azure if I use the SqlCommand object with INSERT INTO statement.

I am using the following technologies:
Microsoft.Data.SqlClient version 5.2.2: (installed with nuget packet manager)
.NET target: Core 8.0
SQL Server version: Microsoft Azure SQL Data Warehouse (RTM) - 12.0.2000.8
Operating system: Windows 11 Home (23H2)

[cheenamalhotra]

@geiraron

Can you provide a repro to understand your usecase better? Are you using distributed transactions or something else that may not be supported by Synapse environment?

[geiraron]

I am not using distributed transaction.

This is a samplecode that shows one of the usecases (except for population of the DataTable):

  using System.Data;
  using Microsoft.Data.SqlClient;
  
  namespace WarehouseTest
  {
      class Program
      {
          static void Main()
          {
              DataTable personnel = new();
              personnel.Columns.Add("UserName", typeof(string));
              personnel.Columns.Add("UserDomainName", typeof(string));
              personnel.Columns.Add("FileName", typeof(string));
              personnel.Columns.Add("WorkSheet", typeof(string));
              personnel.Columns.Add("Row", typeof(int));
              personnel.Columns.Add("Column", typeof(string));
              personnel.Columns.Add("CellValue", typeof(string));
              personnel.Columns.Add("DepartmentNumber", typeof(string));
              personnel.Columns.Add("DepartmentName", typeof(string));
              personnel.Columns.Add("DepartmentKey", typeof(int));
              personnel.Columns.Add("PersonnelCategory", typeof(string));
              personnel.Columns.Add("Year", typeof(int));
              personnel.Columns.Add("Period", typeof(int));
              personnel.Columns.Add("FTE", typeof(double));
              personnel.Columns.Add("PlanVersion", typeof(string));

              personnel.Rows.Add("TestUser", "TestDomain", "Department15.xls","Sheet1", 1, 20, "15", "25", 
                "TestDepartment", 25, "Production", 2024, 9, 15.0, "Forecast");

              string connectionString = @"Server=XXXXXXXXXXXXXXXXXXXXXXXXXXXX.datawarehouse.fabric.microsoft.com; 
              Authentication=Active Directory Interactive; Database=WarehouseTest; Encrypt=Optional;";
  
              using SqlConnection connection = new(connectionString);
              connection.Open();
  
              using SqlBulkCopy bulkCopy = new(connection);
              bulkCopy.DestinationTableName = "Personnel";
  
              bulkCopy.WriteToServer(personnel);
        }
    }
}