Mapping issue with Dataverse FetchXML column missing

[sushiat]

We have an issue with a copy activity using a dataverse connection to one of our CRM entities. The table has some 300 columns and 20k rows and I wrote a fetch xml query to retrieve about 20 of those columns while applying to simple filter conditions.

The connection and query work fine but we noticed that two columns were missing. Turns out that the first 10 rows in the preview only contained NULL values in those columns. Not sure how many rows it inspects when determining the schema of the source table but clearly not all as there are rows containing data.

So when importing the schema in the mappings tab the columns were missing too. And we couldn't add them manually as you can add a new mapping and free type a column name (with warning it might not be present), BUT you can't select a data type for that column. So when running the pipeline, it fails with an error complaining about missing type information.

The only workaround we found was to include two more conditions to force non-NULL values for those columns, then update the schema before removing the filter conditions again. Problem with that is not only that it's pretty annoying, as you need to repeat this every time you want to modify the schema, say add a new column. But what if I need to prepare for a new column the CRM team adds and there simply are now rows with data?

Pretty new to fetch xml but I did read over the documentation to see if there is an attribute one can add to force the return of all-NULL columns but didn't find anything. If anybody knows of a better way please let me know.

Ideally though the schema import task should look at the definition of the table and not rely on whatever first X rows are getting returned. Or at least allow to manually add the mappings with the data types.

[sushiat]

It's cumbersome alright, especially when you have no write permissions and have to involve another department in creating dummy records. So I guess our "temporary filter not-null" can help in that case as it works with a read-only scenario - as long as there is at least some records matching all criteria.

Will check out the mentioned Farbic Ideas portal, thanks for that.

[v-veshwara-msft · Accepted solution]

Hi @sushiat ,
Thnaks for using Microsoft Fabric Community,

Unfortunately, this is a known limitation related to schema inference in Microsoft Fabric (and Azure Data Factory), which can be quite frustrating. The schema inference process typically inspects only a limited number of rows , and if those rows contain only NULL values for certain columns, they may not be included in the inferred schema.

Reference:

Troubleshoot the Dynamics 365, Dataverse (Common Data Service), and Dynamics CRM connectors - Azure ...

One workaround for this is:

Add a dummy row with non-NULL values for the missing columns. This ensures that these columns are detected during schema inference. Once the schema is imported and detected, you can remove the dummy row or filter it out.

Dummy row to detect NULL value Columns 

If you've already attempted Explicit mappings and still faced issues, I recommend the dummy row workaround to get the column into the schema detection process.

We understand how cumbersome this issue can be, especially when dealing with dynamic or frequently changing data. Please consider creating an idea in the Fabric Ideas portal to request a more flexible schema detection feature that could handle columns with NULL values more gracefully. If it gets more votes then Microsoft may consider this to implement in future.

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.

Thank you.