Data Factory Copy Data Upsert Fails if not all target table fields are provided

Question

I have created a Data Factory Pipeline to upload ETL framework configuration data held in json files into equivalent tables in Synapse (dedicated pool)

For one of the tables there are some fields that hold runtime values such as latest watermark value etc therefore when i update the configuration i don't want to overwrite these vales, so these attributes are not contained within the source json files.

I am using a Copy Data task in the pipeline to update the tables and using the Upsert copy Method with the appropriate Key columns defnined.

However I'm receiving the following error complaining that the number of incoming columns do no match the number target columns of target columns.
I was under the impression you could have schema drift/dynamics mapping and that the incoming columns would be auto matched to the target (based on name) and only those target ones would be updated if they are in the source. Is that not the case?

If so what are the alternatives? i really don't want a Mapping Data Flow that takes 5 mins to spin up to update this table.

{  
    "errorCode": "2200",  
    "message": "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.,},],'",  
    "failureType": "UserError",  
    "target": "Import PROCESS",  
    "details": []  
}  

@MartinJaffer-MSFT sorry for tagging but you seem to be very knowledgeable in this area, didn't know if you had any ideas?

Many Thanks

Answer 1

Hello @Matt Evans ,
Thanks for the question and using MS Q&A platform.

As we understand the ask here is how to perform an partial update on SQL side using UPSERT , please do let us know if its not accurate.
As UPSERT stands for UPDATE & INSERT , so it will act on all the columns and not a few .The xecption which you shared is from SQL side . You will run into same issue if you try to do INSERT also with fewer columns .
You can try updating the copy activity and introducing a store proc activity

1. Drop the staging table if its exists .( use the pre copy script option )
2. Dump all the data in staging table ( use the Auto create option )
   
3. Once the staging table is loaded . create a stored precedure and now you can use isnert the records which are not in the base table and Update only those columsn whcih you want .
4. Drop the staging table .

Please do let me if you have any queries.
Thanks
Himanshu

---

• 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

@Yogesh Nikam Can you please tell me how did you solved this problem facing the same issue in synapse. ?