Search code examples
azure-data-factoryupsertsql-timestamp

Azure Data Factory fails with UPSERT for every table with a TIMESTAMP column

my azure data factory throws the error "Cannot update a timestamp column" for every table with a TIMESTAMP column.

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=Cannot update a timestamp column.,Source=.Net SqlClient Data Provider,SqlErrorNumber=272,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=272,State=1,Message=Cannot update a timestamp column.,},],'

I do not want to update the column itself. But even when I delete it from column mapping, it crashes. Here it is not yet deleted:

enter image description here

I get that TIMESTAMP is not a simple datetime and is updated automatically whenever a another column in that row is updated.

The timestamp data type is just an incrementing number and does not preserve a date or a time.

But how do I solve this problem?

Solution

  • I tried to reproduce the issue, and on my ADF, if I remove the timestamp column from mapping the pipeline run with no errors.

    But since this doesn't work for you, here are 2 workaround options:

    Option 1 - on the source, use a query and remove the timestamp column from the query.

    Option 2 - I tried to reproduce your error, and found out that it only happens on upsert. If I use insert, it runs with no error (though it ignore the insert on the timestamp column and increment the timestamp). So you can try to insert to a staging table and then update in sql only the columns you want.