Share via

Sink failed due to Nulls in specific column but in Data Preview there are not Null values

eugenia apostolopoulou 76 Reputation points
2022-03-24T23:56:27.983+00:00

Hello,

I have created a dataflow in order to transfer data from a db table to another but sink operation is continually failing with the same error:
{"StatusCode":"DFExecutorUserError","Message":"Job failed due to reason: at Sink 'New': java.sql.BatchUpdateException: Cannot insert the value NULL into column 'ID', table 'DB_DWH.Myschema.Mytable'; column does not allow nulls. INSERT fails."
However, in Data Preview I can see that the column ID does not contain Null values.
I have already disabled the Auto Mapping and I have mapped manually the input and output columns, I have also checked the 'Allow insert' option but still fails. Furthermore, I have checked the dtypes between the input and output columns an it's a perfect match. I don't know what else to do, any ideas, please??

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,196 questions
Accepted answer
  1. MarkKromer-MSFT 5,206 Reputation points Microsoft Employee
    2022-03-25T03:04:42.897+00:00

    In the data preview, click on the ID column, then click "Statistics". That will tell you how many NULL values are in your data. Alternatively, turn on error row handling in your sink and set it to continue on error. This way, ADF will log all rows that had NULL values.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Nandan Hegde 31,511 Reputation points MVP
    2022-03-25T03:00:46.823+00:00

    Hey,
    The data preview doesnt show all the values.
    As your source is a SQL database, would request you to check whether the source table column has any null values or not
    :Select * from t1 where column is null

    That might help us bebug further