Error with Columnar Index

Wajih Arfaoui 70

Hello, I am creating a pipeline with a Copy activity to get a table from SQL databse to Synapse and I am getting this error : Column '' has a data type that cannot participate in a columnstore index. How can I fix this in ADF ? Thank you

1 answer

Vinodh247 23,266 Reputation points MVP

2024-02-28T11:07:34.5733333+00:00
Hi Wajih Arfaoui,

Thanks for reaching out to Microsoft Q&A.

Disable Columnstore Index:

Try disabling the columnstore index on the target table before running the copy activity. To do this, drop the columnstore index using the following command. Once done try to rerun and check if you face the same issue.

DROP INDEX [index_name] ON [table_name]

If the above is not possible, try Merge method instead of upsert(if you use them).

https://learn.microsoft.com/en-us/answers/questions/1402914/upsert-mode-of-copy-activity-causing-failure-for-d

Did you try using the clustered index?

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.
Please sign in to rate this answer.

1 person found this answer helpful.
Wajih Arfaoui 70 Reputation points

2024-02-28T11:32:30.55+00:00

Hello, thank you for your answer, actually i am enabling autocreating of the table since i am inheriting the schema and it is too long to be manually precised, so how can i exclude columns frm being in index while i don't already have a schema ? Thank you

Vinodh247 23,266 Reputation points MVP

2024-02-28T12:36:52.1866667+00:00

afaik, in cci you cannot directly exclude specific columns from the index

Wajih Arfaoui 70 Reputation points

2024-02-28T12:41:37.5866667+00:00

can we exclude multiple column from index in the same time, or rather define just one ?

Vinodh247 23,266 Reputation points MVP

2024-02-28T12:47:16.6666667+00:00

yes you can! but what about drop and create with only the required cols?

Wajih Arfaoui 70 Reputation points

2024-02-28T13:01:08.17+00:00

Actually what i have now is a table populated with data and as a next step i want to create a upsert activity but first should I drop the columns i don't want to be included as index ?

Vinodh247 23,266 Reputation points MVP

2024-03-01T13:52:05.8066667+00:00

Yes, drop and recreate the index to include only the cols that you want. Dropping and creating the index is not going to harm the data present in your table.

Vinodh247 23,266 Reputation points MVP

2024-03-07T05:50:09.97+00:00

Hi Wajih Arfaoui:

Just checking if you had a chance to look at this. if so, can you please update it?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Error with Columnar Index

1 answer

Your answer