Auto Create in Copy Activity giving Column 'EmpId' has a data type that cannot participate in a columnstore index.',

Question

Auto Create in Copy Activity giving Column 'EmpId' has a data type that cannot participate in a columnstore index.',

Neha 101

I used copy activity to load multiples files to multiple tables according to filename. which means i need to autocreate the tables for new files. However i am getting below error i suppose it is failing while table creation

ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'The statement failed. Column 'EmpId' has a data type that cannot participate in a columnstore index.',Source=,''Type=System.Data.SqlClient.SqlException,Message=The statement failed. Column 'EmpId' has a data type that cannot participate in a columnstore index.,Source=.Net SqlClient Data Provider,SqlErrorNumber=35343,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=35343,State=1,Message=The statement failed. Column 'EmpId' has a data type that cannot participate in a columnstore index.,},],'

File content is very less, simple like below

DeptID,DeptName
1,IT
2,HR
3,Finance
4,Manufacturing

Source - Files in Azure Blob Storage
Destination - Azure Sql Database

Can you please help me with solution

Accepted answer

1 additional answer

Your answer

Answer 1

Bhargava-MSFT 31,261 Microsoft Employee Moderator

Hi @Neha ,
If you are using Azure SQL DW then please select synapse analytics as your dataset. then you should be able to create the table with the "Autocreate" option. Please try and let me know. If have any further questions, please let us know.

Neha 101 Reputation points

2022-07-22T05:28:47.357+00:00

@Bhargava-MSFT @Subashri Vasudevan -I missed your response and tried same thing last night, it seems to be working.
And yes my sink destination was Azure SQL data warehouse DB.

Thanks a lot for your time. We can close the thread
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-07-22T21:12:12.263+00:00
Hi @Neha ,

Glad to know that your issue is resolved. Feel free to reach out to us at any time if you have any further questions.

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

Want a reminder to come back and check responses? Here is how to subscribe to a notification

If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

Subashri Vasudevan 11,231

Hi @Neha ,

Thanks for the ask and for posting it on MS Q&A platform.

Can you please check this link and confirm if your column types aren't any of the listed? Probably the first column is implicitly treated as char(1).

Thanks

Neha 101 Reputation points

2022-07-07T05:25:37.013+00:00

Hi @Subashri Vasudevan - I am not sure what is the column types is being used in create statement , i have enabled the auto create table option in copy activity. I did go through the link you provided but not sure how to check that
Subashri Vasudevan 11,231 Reputation points

2022-07-07T08:47:58.747+00:00

Hey @Neha ,

i tried to replicate the issue but unable to. I am able to auto create with the sample data you provided.

Just couple of question, did the copy activity work for few files atleast? or it fails for all? And is your source a .txt or .csv? or something difffernt

Thanks
Neha 101 Reputation points

2022-07-11T10:17:42.26+00:00

IT is failing for all. i have .csv as file source
Neha 101 Reputation points

2022-07-11T10:42:26.81+00:00

Does it something to have my sql server settings. I tried with .txt but still failing with same error
Subashri Vasudevan 11,231 Reputation points

2022-07-11T11:15:56.607+00:00

To isolate the issue, can you point to your text file directly and see if the table gets created
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-07-12T21:49:49.023+00:00

Hi @Subashri Vasudevan , Thank you for your help.

Hi @Neha ,
By looking at the error, It looks like your destination is the Azure SQL Data warehouse(since you are using auto-create for the table creation)

If yes, by default SQL DW creates tables with clustered columnstore index. Your destination columns shouldn't be any of the below data types as mentioned by @Subashri Vasudevan

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-columnstore-index-transact-sql?view=sql-server-ver16#LimitRest

Can you please try to create the destination table first and then do the copy? please let me know if it works.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-07-12T22:18:15.173+00:00

Hi @Subashri Vasudevan and @Neha ,
I was able to reproduce the error using Azure SQL DW(Synapse SQL pool) as a sink.
@Neha , please confirm if your destination is Azure SQL data warehouse DB.

Share via

Auto Create in Copy Activity giving Column 'EmpId' has a data type that cannot participate in a columnstore index.',

1 additional answer

Your answer