azure-sql-databaseazure-data-factoryazure-synapseupsert

ADF - Column cannot participate in columnstore index

I have a table I have created in Azure Synapse that is a map from a Azure SQL source table in a database and is defined as

CREATE TABLE [schemaName].[Notes]
( 
    [Id] [int]  NOT NULL,
    [Title] [nvarchar](MAX) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    HEAP
)

When using a copy activity with UPSERT in Azure Data Factory, I get the following error in the pipeline:

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=The statement failed. Column 'Location' has a data type that cannot participate in a columnstore index.,
Source=Framework Microsoft SqlClient Data Provider

Why am I getting this error since there is no column store index on the table? Is this an issue with the upsert activity?

Solution

  • The issue I was experiencing was not with the source or sink tables, but with a interim table that ADF creates in-between for the upsert activity. This table by default appears to be created with a columnstore index.

    To solve for this, I switched from doing an upsert to bulk inserting into a interim table, then manually merging those tables with a script.