none
ADF DATA FLOW Returning Null error RRS feed

  • Question

  • I am inserting records into a table where a column called TenantID is not nullable. I have added this column as cross join into my data flow, and during preview it seems to have the value

    Preview

    But when I debug the pipeline, encountered following error :

    Cannot insert the value NULL into column 'TenantID', table 'dbo.Item'; column does not allow nulls. INSERT fails.\njava.sql.BatchUpdateException: Cannot insert the value NULL into column 'TenantID', table 'dbo.Item'; column does not allow nulls. INSERT fails.\n\tat




    How to troubleshoot further to understand which row is returning null.I did a cross join, hence all valued should have been applied


    R.S.S.

    Monday, December 30, 2019 2:42 PM

All replies

  • Hello R.Singh , 

    We understand that the sink is SQL ( or similar) , can you please let us know what is the source . The preview always shows a subset of the actual data and not all the records . 

    If you are using SQL , you can use SSMS to run the query to check all the records and may be adding 

    TenantID is NULL 

    Helps you to get to the record .
    please let me know how it goes .


    Thanks Himanshu

    Monday, December 30, 2019 6:29 PM
    Moderator
  • Dataflow preview runs on a subset of rows(as described in your debug settings). You may actually have a data quality problem with NULL values. You can check this in dataflow by adding a filter after the join and rerouting the records if they are NULL.
    • Proposed as answer by dataflowuser Monday, December 30, 2019 7:47 PM
    Monday, December 30, 2019 7:47 PM
  • The source is actually from on premise SQL server. Hence in pipeline, I pushed the data into Azure BLOB as JSON, before picking it up in data flow.

    TenantID is not from source, but inserted in between as parameter.

    Even after trying to handle this via Derived column step, it still returns null.


    Derived columns

    I wish the post display screen will lead to exact row, but its no help


     

    R.S.S.


    • Edited by R. Singh Tuesday, December 31, 2019 3:21 AM Updated image
    Tuesday, December 31, 2019 2:59 AM
  • I suppose this data is relational and can be staged as csv instead of JSON. Post the dataflow "script" and it will help tell what is wrong. I guess the staging in copy activity is the culprit.
    • Proposed as answer by Spam filter Wednesday, January 1, 2020 5:09 PM
    Wednesday, January 1, 2020 5:09 PM