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

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

jsrichards — Tue, 13 Feb 2024 00:34:04 GMT

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.

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

rubayatyasmin — Tue, 13 Feb 2024 06:14:56 GMT

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.

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

jsrichards — Wed, 14 Feb 2024 02:41:31 GMT

Hi rubayatyasmin

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

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

rubayatyasmin — Wed, 14 Feb 2024 08:00:38 GMT

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

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

jsrichards — Wed, 14 Feb 2024 22:46:41 GMT

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

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

v-cboorla-msft — Mon, 19 Feb 2024 14:34:13 GMT

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

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

Element115 — Mon, 04 Mar 2024 17:08:10 GMT

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

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

miguel — Mon, 04 Mar 2024 19:01:07 GMT

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

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

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

Element115 — Mon, 04 Mar 2024 19:12:28 GMT

Only programmatically, I guess? No GUI checkbox anymore?

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

miguel — Mon, 04 Mar 2024 21:29:19 GMT

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.

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

jsrichards — Wed, 06 Mar 2024 00:13:54 GMT

Thanks Miguel, this fixed the issue 🙂