Error with Columnar Index

Question

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

0 comments

1 answer

Your answer

Answer 1

Vinodh247-1375 43,181 Volunteer Moderator

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.

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-1375 43,181 Reputation points Volunteer Moderator

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-1375 43,181 Reputation points Volunteer Moderator

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-1375 43,181 Reputation points Volunteer Moderator

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-1375 43,181 Reputation points Volunteer Moderator

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?

Share via

Error with Columnar Index

1 answer

Your answer