Move data from Data Lake Store (JSON file ) to Azure Search using Azure Data Factory

Question

I am trying to move a json file from Azure Data Lake to Azure Search using Data Factory. But it is showing the following error,

Copy activity encountered a user error at Source side: ErrorCode=UserErrorSourceDataContainsMoreColumnsThanDefined,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error found when processing 'Csv/Tsv Format Text' source 'file.json' with row number 1: found more columns than expected column count: 52.,Source=Microsoft.DataTransfer.Common,'.

How can we move a data from json file to Azure Search?

Update1: I have used JSON format in the source but in the destination Azure Search the createddate column is a DateTimeOffset type column (String -> DateTimeOffset). So got the below error,

Copy activity encountered a user error at Source side: ErrorCode=UserErrorInvalidDataValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 'createddate' contains an invalid value '1/5/2017 2:03:55 PM'. Cannot convert '1/5/2017 2:03:55 PM' to type 'DateTime' with format 'yyyy-MM-dd HH:mm:ss.fffffff'.,Source=Microsoft.DataTransfer.Common,''Type=System.FormatException,Message=String was not recognized as a valid DateTime.,Source=mscorlib,'.

I tried to change the Datetime Format in the Soure side, but nothing worked because of the json file. When we try to get the data from json all data will be of string type.

Update2:

When I finished that Datetime error, then following error is coming

Copy activity encountered a user error at Sink side: ErrorCode=UserErrorAzuerSearchOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error happened when writing data to Azure Search Index 'searchindex'.CloudException RequestId: '153be0ce-0bda-4722-8c9e-951b5325eaa8'.Status Code: 'BadRequest'.,Source=Microsoft.DataTransfer.ClientLibrary.AzureSearch,''Type=Microsoft.Rest.Azure.CloudException,Message=The request is invalid. Details: actions : 0: Document key cannot be missing or empty.

Please suggest me a solution to overcome this one.. Thanks in Advance !

Answer 1

Looks like you specified the source format as "TextFormat" instead of "JsonFormat" in the input ADLS dataset. Refer to JSON format on the corresponding supported settings with details and samples.

If you are using ADF v1, you can try author via the copy wizard which will guide you through the configurations in UI.

Answer 2

I have used createddate.Value.ToString("yyyy-MM-dd HH:mm:ss.fffffff") instead of createddate.ToString("yyyy-MM-dd HH:mm:ss.fffffff") and it works fine because createddate is a nullable column (Nullable).

createddate.Value.ToString("yyyy-MM-dd HH:mm:ss.fffffff")

Answer 3

The Error "Document key cannot be missing or empty" means the Azure Search Index Key is empty in the row you want to insert into Azure Search. Could you double check whether the Key column is provided in source side and always not null?