Forum Discussion

JasonYeung's avatar
JasonYeung
Brass Contributor
Apr 13, 2023

How to handle null values in Data Factory

Hi,

I created a pipeline in Azure Data Factory that grabs data from a REST API and inserts into an Azure table. The pipeline looks like the following:

The pipeline failed with the following error:

"ErrorCode=UserErrorSchemaMappingCannotInferSinkColumnType,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Data type of column 'responder_id' can't be inferred from 1st row of data, please specify its data type in mappings of copy activity or structure of DataSet.,Source=Microsoft.DataTransfer.Common,'"

 

The API returns a JSON file and when I previewed the JSON file, I noticed that the "responder_id" is null:

 

 

In the sink's mapping, it's set to Int64. 

The group_id and requester_id is the same type, but because there is a value for these in teh JSON file, it processed these fine. I was wondering how do I change this so null is accepted? These 2 fields may be null in the JSON file for other records and I'd like for null's to be accepted.

 

Jason

 

1 Reply

  • LiamOB's avatar
    LiamOB
    Brass Contributor
    Oct 16, 2023
    I'm facing a similar problem that I'm trying to find an answer to. In my case, I have Access as a sink with a self-hosted integration runtime (yes, I know Access is very legacy, but it's a client requirement). The data types all line up between the SQL view source and the Access database sink, but it seems that requardless of whether I map or don't map the column's, if there's a null date value in the first row, ADF (or the self-hosted integration runtime?) insists on picking an erroneous data conversion. This results in an error the first time a non-null date occurs in a column where the first row had a null value.

Resources