Gen2 Dataflow load to Lakehouse error - Can't insert null data into non-nullable column

[jsrichards]

I am trying to load a handful of tables from Business Central to a Fabric Lakehouse using Gen2 Dataflows and the BC connector but keep getting the following error. For reference, I have the dataflow set to create the table and replace any existing data.

104100 Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: DataFormat.Error: Error in replacing table's content with new data in a version: #{0}., InnerException: We can't insert null data into a non-nullable column., Underlying error: We can't insert null data into a non-nullable column. Details: Reason = DataFormat.Error;Message = We can't insert null data into a non-nullable column.;Message.Format = We can't insert null data into a non-nullable column.;Microsoft.Data.Mashup.Error.Context = User

I understand what the error message is saying but am confused about why the columns created by the dataflow are not nullable by default. Is there a way to specify the columns to be nullable? I'd like to avoid manually creating the tables in a notebook as some can be hundreds of columns long.

[miguel · Accepted solution]

Please check this section of the documentation around cases like this:

Dataflow Gen2 data destinations and managed settings - Microsoft Fabric | Microsoft Learn

[jsrichards]

Thanks Miguel, this fixed the issue 🙂

[Element115]

Only programmatically, I guess?  No GUI checkbox anymore?

[miguel]

I'm not aware of something in the UI in the past as a checkbox for this. We're aware of this and we plan on addressing this scenario in a near future.

[rubayatyasmin]

That's sad. I getting new kind of errro in my prod env. Btw, you can follow the thread bellow. Maybe you need to contact the support team. 

Solved: Re: Gen2 Dataflow load to Lakehouse error - We can... - Microsoft Fabric Community

[jsrichards]

Thanks, unfortunately that thread didn't help so I ended up raising a support ticket.

[v-cboorla-msft]

Hi @jsrichards 

Thanks for using Microsoft Fabric Community.

Apologies for the inconvenience that you are facing here.

If you have raised support ticket could you please provide the ticket number here as we can keep an eye on it.

Thanks

[rubayatyasmin]

Hi, @jsrichards 

try the process again form the start. make sure your source has the allow null option selected. I am loading data using gen2 that have null values and it is refreshing successfully. 

[Element115]

Where is this 'allow null values' checkbox?  Don't see it anywhere?

[jsrichards]

Hi @rubayatyasmin 

I don't see an 'allow null' option in the data source.