question

braxx avatar image
0 Votes"
braxx asked EvanBelkin-4079 commented

Pipeline failed when parsing json due to null values

First of all, I am new in ADF world.

Recently got a task which I am still strugling with. I am trying to parse nested json stored in Blob Storage and copy the content to a table I had built in Azure Sql DB.

I created a pipeline and mapping but when running I am getting the following error:

 Operation on target CopyJsonTo Sql failed: ErrorCode=UserErrorSchemaMappingCannotInferSinkColumnType,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Data type of column 'dimension1' 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,'

I guess it is because some of my data contains null values. here the json file sample:
22762-capture4.png


Here are also some screenshots from my pipline configuration for source, sink and mapping
22690-capture5.png


22781-capture6.png


22782-capture7.png



Thanks in advance. Any suggestion will be valuable to me

azure-data-factory
capture4.png (66.4 KiB)
capture5.png (43.4 KiB)
capture6.png (48.5 KiB)
capture7.png (43.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered EvanBelkin-4079 commented

Hello @BartoszWachocki-4076 and welcome to Microsoft Q&A and Data Factory. Thank you for your question.

I was able to reproduce your issue... until I hooked up the SQL. However I think I know where to fix this anyway.
First, go to your sink dataset
22699-image.png
and import schema
22768-image.png
Then go back to the copy activity mapping and import schemas again (to ensure it is up to date). If you click clear first, don't forget to check the collection reference.
22744-image.png

The idea behind this, is to make the mapping explicit to Data Factory, so it doesn't try to guess data type from the data.
Please let me know if this solves your issue. If not, we can try the same on the source side, and if that doesn't work, let me know and I'll figure something out.

Welcome and Thank you
Martin


image.png (61.6 KiB)
image.png (56.5 KiB)
image.png (80.4 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I like this new Q&A portal and you guys from msft for your rapid responses and relevant solutions.
You saved me a few hours of frustrations and hope better sleep tonight.

I was able to load a couple of jsons with success, so may say it works :)
Thanks a lot!

0 Votes 0 ·

Thank you for your positive feedback @BartoszWachocki-4076 . Hearing how I helped really means a lot to me. Thank you!

0 Votes 0 ·

Hi Martin,
How would you do this when you have a dynamic dataset?
Thanks

0 Votes 0 ·