Doing Upsert in Azure Data Factory with Copy Data action fails

user_19156090 0
Viewed 858

I have a source table in Azure SQL Database with employees. It's a staging table in an ETL environment. Then I have a destination table called DimEmployee.

I want to use Upsert with the Copy data action. In DimEmployee I have a primary key column with identity(1,1) called EmployeeID. If I do a plain Insert action in Sink to DimEmployee it works after mapping the fields. EmployeeID is not mapped becuase it's an identity field.

If I change to Upsert and set the key field that is the link between the tables I get an error. It says it can't insert NULL into EmployeeID. But that field is an autoincrement field and primary key.

Anyone experienced this?

I've attached the error message I've got.

enter image description here

azure-data-factory azure-sql-database
1 Answers

The error message is referring to the interim table rather than to the sink table itself. The interim table is created when the copy activity write behaviour is to upsert, but not when the write behaviour is to insert, hence your insert completed without error.

The use of an interim table is mentioned here; it is created in tempdb by default. I couldn't find any documentation but used a trace to observe that it is created by a SELECT INTO:

IF OBJECT_ID('[##InterimTable_14d7d364-393c-4b28-bb92-7ee742827b85]', 'U') IS NULL BEGIN select * into [##InterimTable_14d7d364-393c-4b28-bb92-7ee742827b85] from [dbo].[foo_tbl] where 1 = 2 union select * from [dbo].[foo_tbl] where 1 = 2 alter table [##InterimTable_14d7d364-393c-4b28-bb92-7ee742827b85] add BatchIdentifier BIGINT IDENTITY(1,1) END

The inclusion of a UNION in the SELECT INTO means that the IDENTITY property is not inherited from the column in the sink table, but the column is created with a NOT NULL constraint, as per Microsoft documentation.

This is what is causing your error.

My workaround was to change the write behaviour to Stored procedure, creating a sproc to implement the MERGE logic and using a table type based on the source dataset as a parameter. This parameter was then specified as the sink dataset.

user_158561110
Related