Data Factory does not support Upsert on uniqueidentifier key columns

Question

Hi Team.

I'm using ADF to sync data from a storage account to SQL Server

I'm getting this error when I try to upsert into a SQL server table using an uniqueidentifier column as a key columns, why this is not supported it? also, the message error does not make any sense 

 

Operation on target copy_parquet_to_wl failed: 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=The data type xml cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.,Source=.Net SqlClient Data Provider,SqlErrorNumber=5335,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=5335,State=1,Message=The data type xml cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.,},],'

Answer 1

@Anonymous 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 types like 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.

Answer 2

Hi.

I tried the upsert inside the data flow, but it seems that is not supported with self-hosted IR, I do not have another option since the database is located inside a VM.

Linked service with Self-hosted Integration runtime is not supported in data flow. Please utilize the Azure IR with managed vnet using this tutorial