@Javier Guerrero Thanks for your response and additional clarification. I did additional testing by adding an additional column of datatype xml to my SQL table and tried to do an upsert using copy activity and was able to reproduce the issue you are experiencing.
Hence, I did some internal analysis and noticed that few
Image type ,
geography type ,
xml type are not supported in Upsert function of copy activity as Copy activity upsert leverage
Union . And these types are not supported in
Union . Hence this confirms that upsert feature in Copy activity has some limitations with respect to
types which is not well documented. I'll provide a feedback to respective product team to update the ADF Azure SQL documentation to call out any limitations related to upsert functionality so that other users can avoid this issue.
Workaround: Since this issue related to a limitation, the possible workaround is to use Mapping data flow instead of Copy activity.
In mapping data flow you can use Alter tranformation to define the upsert condition and then followed by a sink transformation to upssert the data to your Azure SQL table.
Below is a sample flow of dataflow look like:
Here is a demonstration on how to use Alter tranformation for doing upsert/delete/insert data into SQL table: Alter Row Transformation in Mapping Data Flow in Azure Data Factory
I did tested this scenario using Mapping data flow instead of Copy activity and the xml type is upserted as expected.
If you have any additional feedback regarding the upsert funcationality in Copy activity, please feel free to log feedback in Azure Data factory IDEAS forum here and do share the link back so that we can share it with internal team to further review it.
Hope this helps.
Please don’t forget to
Accept Answer and
Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.
hi, thank you for your response :)
this morning I did a POC and it worked, however when I tried it with the real table it does not work,
this is my source definition, it's a parquet file
table sink definition: