Share via

Synapse Copy Data Upsert Fails due to Column count in target table does not match column count specified in input

Dengyu Xie 0 Reputation points
Apr 27, 2023, 10:09 PM

Hi,

I am trying to use Copy Data activity in Synapse Analytics pipeline and get below error, the source db (No-SQL) schema is NOT fixed, the target is SQL table which has fixed schema, there are some rows which do not contain all the fields in the mapping, I did some research and found some similar page (https://learn.microsoft.com/en-us/answers/questions/823145/data-factory-copy-data-upsert-fails-if-not-all-tar?source=docs), however it is for ADF, do you have similar solution for Azure Synapse Analytics? Thank you.

Operation on target Copy CosmosDB for MongoDB Data Flow failed: Failure happened on 'Sink' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Column count in target table does not match column count specified in input. If BCP command, ensure format file column count matches destination table. If SSIS data import, check column mappings are consistent with target.,Source=.Net SqlClient Data Provider,SqlErrorNumber=107098,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=107098,State=1,Message=Column count in target table does not match column count specified in input. If BCP command, ensure format file column count matches destination table. If SSIS data import, check column mappings are consistent with target.,},],'
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,016 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. HimanshuSinha-msft 19,476 Reputation points Microsoft Employee
    Apr 28, 2023, 6:33 PM

    Hello @Dengyu Xie ,

    Thanks for the question and using MS Q&A platform.

    Yes, I can confirm that the above solution will work for Synapse also. You will see a a similar UI in Synapse data pipeline, sharing that below.

    User's image

    Thanks

    Himanshu

    Please accept as "Yes" if the answer provided is useful , so that you can help others in the community looking for remediation for similar issues. 


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.