英文:
Azure Data Factory fails with UPSERT for every table with a TIMESTAMP column
问题
我的Azure数据工厂针对每个具有TIMESTAMP列的表抛出错误"无法更新时间戳列"。
> ErrorCode=SqlOperationFailed，'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException，Message=数据库操作失败。请搜索错误以获取更多详细信息。Source=Microsoft.DataTransfer.ClientLibrary，'Type=System.Data.SqlClient.SqlException，Message=无法更新时间戳列。Source=.Net SqlClient Data Provider，SqlErrorNumber=272，Class=16，ErrorCode=-2146232060，State=1，Errors=[{Class=16，Number=272，State=1，Message=无法更新时间戳列。}],
我不想更新列本身。但即使我从列映射中删除它，仍会崩溃。在这里尚未删除它：
我明白TIMESTAMP不是简单的日期时间，它会在该行的其他列更新时自动更新。
> 时间戳数据类型只是一个递增的数字，不保留日期或时间。
但我该如何解决这个问题呢？
英文:
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:
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?
答案1
得分: 1
我尝试复现这个问题，在我的ADF上，如果我从映射中删除时间戳列，管道运行不会出现错误。
但由于这对您来说不起作用，这里有两种解决方法：
选项1 - 在源上，使用查询并从查询中删除时间戳列。
选项2 - 我尝试复制您的错误，并发现它只在 upsert 时发生。如果我使用 insert，它会无错误运行（尽管它会忽略时间戳列上的插入并增加时间戳）。因此，您可以尝试插入到临时表，然后仅在 SQL 中更新您想要的列。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论