Share via

Azure Data Factory - How to get the column that is failing?

Abhijit Shrikhande 317 Reputation points
2022-11-02T17:55:14.987+00:00

Hello all,
I am getting an error that looks like this. I wish ADF would tell me which is the column that is showing up a NULL value? The source in this case is a JSON text file and the destination is a database table in SQL Server.
Every column in the destination DB table is nullable, so as per my understanding this error shouldn't show up.

ErrorCode=UserErrorTypeInSchemaTableNotSupported,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to get the type from schema table. This could be caused by missing Sql Server System CLR Types.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.InvalidCastException,Message=Unable to cast object of type 'System.DBNull' to type 'System.Type'.,Source=Microsoft.DataTransfer.ClientLibrary,'

I can't keep on remapping the entire schema as it has more than 120 columns and the JSON is in a structured format.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,196 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee
    2022-11-03T18:06:44.477+00:00

    Hello @Abhijit Shrikhande ,

    Thanks for the question and using MS Q&A platform.

    From your statement, seems like you are using explicit mapping in your copy activity. The above error occurs when your source data or any of your source column is empty without values and you are using explicit mapping for that column. And when Type is not specified in the column mapping, ADF cannot detect the type from source as source is empty. To fix it or workaround it, it is recommended to either remove the column mapping (nothing but use auto-mapping) or specify the type in column mapping.

    I do agree with you that displaying the column name in error message that is having empty would definitely be helpful and will help users to self troubleshoot the issue. I would recommend submitting a feature request in IDEAS forum for the same here - https://feedback.azure.com/d365community/forum/1219ec2d-6c26-ec11-b6e6-000d3a4f032c . All the feedback shared in this forum are actively monitored and reviewed by respective product owners.

    Please do share the feedback link once it is submitted so that I can take it forward to respective product owners to further review it. Sharing it here also will help other users with similar feedback to up-vote and comment on it to help increase the priority of the feature request.

    Hope this info helps. Please let us know how it goes.

    Thank you