Data Factory does not support Upsert on uniqueidentifier key columns

Question

Data Factory does not support Upsert on uniqueidentifier key columns

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.,},],'

KranthiPakala-MSFT 36,017 Reputation points Microsoft Employee

2023-03-07T23:29:05.4633333+00:00

Hi @Javier Guerrero ,

Welcome to Microsoft Q&A forum and thanks for reaching out here.

I'm not sure how you came to a conclusion that Data Factory does not support Upsert on uniqueidentifier key columns. To verify the behavior you have reported I have created a sample test as below and I don't see any issues with Upsert on uniqueidentifier key columns. My pipeline ran successfully without issues.

Upsert on my Azure SQL table went through as expected.

Source data:

Output:

And also you are doing a upsert and the error message doens't seem relevant. Looks like there is something else causing this issue. Could you please confirm if there are any column with datatype xml in you sink SQL table or source data?

To better assist on this issue, could you please help provide a sample of source file with few records and also the schema definition of your sink SQL table so that I can try to replicate the issue and identify the root cause.

In addition, please do share your failed pipeline runID and copy activity runID.

Thank you
Javier Guerrero 21 Reputation points

2023-03-07T23:53:44.41+00:00
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

sink

Mapping

table sink definition:

CREATE TABLE [dbo].[testtable]( [columnkey1] [uniqueidentifier] NOT NULL, [test1] [int] NULL, [test2] [varchar](50) NULL, [test3] [bigint] NULL, [test4] [datetime] NULL, [test5] [int] NULL, [columnkey2] [int] NOT NULL, [test6] [xml] NULL, [test7] [xml] NULL)

KranthiPakala-MSFT 36,017 Reputation points Microsoft Employee

2023-03-07T23:29:05.4633333+00:00

Hi @Javier Guerrero ,

Welcome to Microsoft Q&A forum and thanks for reaching out here.

I'm not sure how you came to a conclusion that Data Factory does not support Upsert on uniqueidentifier key columns. To verify the behavior you have reported I have created a sample test as below and I don't see any issues with Upsert on uniqueidentifier key columns. My pipeline ran successfully without issues.

Upsert on my Azure SQL table went through as expected.

Source data:

Output:

And also you are doing a upsert and the error message doens't seem relevant. Looks like there is something else causing this issue. Could you please confirm if there are any column with datatype xml in you sink SQL table or source data?

To better assist on this issue, could you please help provide a sample of source file with few records and also the schema definition of your sink SQL table so that I can try to replicate the issue and identify the root cause.

In addition, please do share your failed pipeline runID and copy activity runID.

Thank you
Javier Guerrero 21 Reputation points

2023-03-07T23:53:44.41+00:00

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

sink

Mapping

table sink definition:

CREATE TABLE [dbo].[testtable]( [columnkey1] [uniqueidentifier] NOT NULL, [test1] [int] NULL, [test2] [varchar](50) NULL, [test3] [bigint] NULL, [test4] [datetime] NULL, [test5] [int] NULL, [columnkey2] [int] NOT NULL, [test6] [xml] NULL, [test7] [xml] NULL)

Answer 1

@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.

User's image

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:
User's image

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.

Javier Guerrero 21 Reputation points

2023-03-08T13:00:04.7466667+00:00

Great, thanks. :)

Answer 2

Javier Guerrero 21

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

KranthiPakala-MSFT 36,017 Reputation points Microsoft Employee

2023-03-11T16:42:14.3733333+00:00

@Javier Guerrero Thanks for sharing this additional information here as it will be helpful for other community community members.

SHIR is not supported in Mapping dataflows and hence recommend using Azure IR with Vnet and Private endpoints.

Thank you

Data Factory does not support Upsert on uniqueidentifier key columns

1 additional answer

Activity