CODEWITHSUNDEEP
azure-sql-databaseazure-data-factoryazure-synapseupsert
Daniel
Daniel

Reputation: 75

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?

Upvotes: 0

Views: 135

Answers (2)

Daniel
Daniel

Reputation: 75

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.

Upvotes: 0

Pratik Lad
Pratik Lad

Reputation: 8402

The statement failed. Column 'Location' has a data type that cannot participate in a columnstore index.,

As per documentation, the issue you're experiencing occurs because the following column data types cannot be included in a columnstore index, and SQL DW by default creates tables with clustered columnstore indexes.

nvarchar(max), varchar(max), and varbinary(max) (Applies to SQL Server 2016 and prior versions, and nonclustered columnstore indexes)

You can change the datatype of column if it is included in above list to avoid the error.

You can Drop the index from the table. You can use the following T-SQL command to Check if your table has any columnstore index and drop the columnstore index:

--check if your table has any columnstore index
select * from sys.indexes AS i
INNER  JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
WHERE is_hypothetical = 0  AND i.type in (5, 6)
AND i.object_id = OBJECT_ID('dbo.Results');

-- drop the columnstore index
DROP INDEX [index_name] ON [table_name]

Upvotes: 0

Related Questions