Synapse/ADF pipeline error 2200 on upserting CSV into on-prem SQL-server table

Question

In my pipeline I perform an upsert from a flat CSV file (semicolon separated) into a table in our on-premise SQL-server. At first all the data types from the CSV file are read as STRING and I did the same with the SQL table, which means that I set all the fields to NVARCHAR(500). This worked fine, the upsert is based on an id field (EMPLOYEE_ID).

This CSV file is coming from another application (also MS-SQL based). So that why I changed all the NVARCHAR(500) fields to the correct ones coming from the source database.

Now I've done that I'm getting this error:

{
    "errorCode": "2200",
    "message": "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 text 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 text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.,},],'",
    "failureType": "UserError",
    "target": "EMP upsert",
    "details": []
}

I'd see that it's trying to upsert a text field to do the UNION. But I'm not using a text field to match on. Can somebody tell me what I'm doing wrong and how to solve it?

I've refreshed all the schema definitions in Azure to make sure that it sees all the datatypes correctly.

score 0 · Answer 1 · answered Nov 17 '22 at 10:51

I tried to reproduce the issue in my environment and ended up with same error.

enter image description here

The cause of error can the logic of upsert it takes the column as a key and compare the value with both source and destination and based on this it updates or insert records.
Text, xml, ntext these datatypes are not comparable
The workaround for this issue is to cast the column with TEXT data type with Use nvarchar(max), varchar(max) and varbinary(max) instead.

Form Microsoft Document,

The ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

I casted the column with Text datatype to varchar(max) in Pre-copy script so it will alter the column datatype before the upsert operation.

Query:

ALTER TABLE dbo.yourtablename
ALTER COLUMN yourTextcolumnname VARCHAR(MAX)

enter image description here

Pipeline executing successfully:

enter image description here

Okay, I see. But in that case I could also modify all the fields in the sink table isn't it? I mean, I've changed the whole sink table to the field types of the original source where the CSV file is based on. If this isn't possible and I have to alter all the columns in the sink table it might be better to have all the fields in the sink table as a NVARCHAR field, that would safe some work ;) — AntonyJ, Nov 18 '22 at 12:17
Yes, correct you have to changed the whole sink table to the field nvarchar,varchar or binary any one of this — Pratik Lad, Nov 24 '22 at 18:53

Synapse/ADF pipeline error 2200 on upserting CSV into on-prem SQL-server table

1 Answers1