ADF data flow Sink failed due to Nulls in specific column, insert error to sink enabled with staging

Question

Hello,

In a data factory pipeline, the following dataflow returns the below error, when trying to insert to the sink. The sink exports to Az SQL DW - synapse SQL Pool, and the mapped column in DW, does not have any NULLs.

The pipeline trigger fail is intermittent. Staging is enabled on the sink, and is using the sink schema.

Error:

Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.********'; column does not allow nulls. INSERT fails.

(The column, is also listed as one of the key columns in the sink settings)

6 columns

Alter row transformation from source: upsertif --> true()

Sink settings:

Sink type: Dataset

schema drift enabled

2 Key columns

Schema mapping done manually.

Update method: Only allow upsert is enabled

Full error:

Operation on target upsertsyncplanrequestsToDW failed: {"StatusCode":"DFExecutorUserError","Message":"Job failed due to reason: at Sink 'sink1': [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot insert the value NULL into column 'columnx', table 'tempdb.dbo.sometable_sdf615e6_9'; column does not allow nulls. INSERT fails. Additional error <2>: ErrorMsg: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The statement has been terminated., SqlState: 01000, NativeError: 3621 ","Details":"java.sql.BatchUpdateException: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot insert the value NULL into column 'columnx', table 'tempdb.dbo.sometable_sdf615e6_9'; column does not allow nulls. INSERT fails. Additional error <2>: ErrorMsg: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The statement has been terminated., SqlState: 01000, NativeError: 3621 \n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerStatement.executeBatch(SQLServerStatement.java:1886)\n\tat com.microsoft.dataflow.transformers.store.JDBCWriter.executeBatchSQLs(JDBCStore.scala:575)\n\tat com.microsoft.dataflow.transformers.store.JDBCWriter.executeSQL(JDBCStore.scala:550)\n\tat com.microsoft.dataflow.transformers.store.JDBCWriter$$anonfun$executeTableOpAndPostSQL$1.apply$mcV$sp(JDBCStore.scala:644)\n\tat com.microsoft.dataflow.transformers.store.JDBCWriter$$anonfun$executeTableOpAndPostSQL$1.apply(JDBCStore.scala:644)\n\tat com.microsoft.dataflow.transformers.store.JDBCWriter$$anonfun$execute"}

Thanks in advance. Any suggestions please.

Answer 1

Hi @Kellwyn Edwin ,

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

After having a look at the error message, it suggests that there was a failure in the upsert operation on the target table. The cause of the error is that the column "columnx" does not allow null values, but a null value was attempted to be inserted into the column.

You can check the mapping in the activity to make sure that the source column is mapped to the correct sink column and that the source column has a value. You can also modify the source data to ensure that the column has a value (may you can use iifNull() function to manually change the specific column from null to empty string value.) before attempting the upsert operation.

You may also try exploring below error row handling mechanism and implement in your data flow settings to overcome the problem:

Ref doc: Error row handling

In additional you can also use conditional splitting of rows with null values from the rows without null values in any of the source columns and then fix the null values with valid values ( iifNull() function to manually change the specific column from null to empty string value.) and do upsert to the destination:

Ref doc: Check for NULLs in all columns

Hope this info helps. Let us know how it goes.

---

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.