What causes DF-MSSQL-InvalidDataType?

The SQL table contains sql_variant, hierarchyid, geometry, geography, or XML columns that ADF cannot map to a supported Spark type. A sink column was changed from a supported type (e.g., nvarchar) to an unsupported type (e.g., sql_variant) without updating the ADF schema. The schema was imported when the table had different column types, and the stored ADF schema is now stale. An explicit CAST to an unsupported SQL Server type is in the derived column expression

How do I fix DF-MSSQL-InvalidDataType?

In ADF Studio, open the failing Source or Sink transformation and click 'Import schema' to refresh the column list.. Identify the problematic column from the ADF Monitor error detail (it shows the column name and type).. Add a Derived Column transformation to cast the unsupported column to STRING: `toString(problematicColumn)`.. For geometric or geographic types, consider converting to WKT (Well-Known Text) in SQL using `yourColumn.STAsText()` before ADF reads it.. If the column is not needed in the data flow output, exclude it by removing it from the Source projection.

Medium severitydata source

Power BI Refresh Error:
DF-MSSQL-InvalidDataType

What does this error mean?

A column in the SQL Server source or sink has a type that ADF Mapping Data Flows cannot map to a Spark type — SQL Server-specific types like sql_variant, hierarchyid, geometry, or geography. ADF's schema import may silently map these to unsupported types, and the error only surfaces at runtime when Spark tries to read or write the column.

Common causes

1The SQL table contains sql_variant, hierarchyid, geometry, geography, or XML columns that ADF cannot map to a supported Spark type
2A sink column was changed from a supported type (e.g., nvarchar) to an unsupported type (e.g., sql_variant) without updating the ADF schema
3The schema was imported when the table had different column types, and the stored ADF schema is now stale
4An explicit CAST to an unsupported SQL Server type is in the derived column expression

How to fix it

1In ADF Studio, open the failing Source or Sink transformation and click 'Import schema' to refresh the column list.
2Identify the problematic column from the ADF Monitor error detail (it shows the column name and type).
3Add a Derived Column transformation to cast the unsupported column to STRING: `toString(problematicColumn)`.
4For geometric or geographic types, consider converting to WKT (Well-Known Text) in SQL using `yourColumn.STAsText()` before ADF reads it.
5If the column is not needed in the data flow output, exclude it by removing it from the Source projection.

Frequently asked questions

Which SQL Server types does ADF not support in data flows?

ADF Mapping Data Flows cannot natively handle sql_variant, hierarchyid, geometry, geography, and XML columns. These must be cast to a string or binary representation before ADF reads or writes them.

How do I avoid breaking ADF when someone adds a new column to the SQL table?

Enable 'Allow schema drift' in the Source transformation. This lets the data flow continue when new columns appear — add explicit casts for known problematic types as a safeguard.

Can I convert geometry columns to a usable format without changing the source table?

Yes — in a SQL query-based source, use `yourColumn.STAsText() AS yourColumnText` to convert geometry to WKT format. ADF can then read the resulting NVARCHAR column without a type error.

Does re-importing the schema fix this error?

Only if the mismatch is due to a stale ADF schema. If the table genuinely contains unsupported types, you still need a cast transformation — re-importing updates ADF's schema knowledge but doesn't change type handling.

Official documentation: https://learn.microsoft.com/en-us/azure/data-factory/data-flow-troubleshoot-guide