question

TariqJamal-0790 avatar image
0 Votes"
TariqJamal-0790 asked TariqJamal-0790 commented

JSON Array Object Mapping in ADF

We currently have an API end point that we call and it returns JSON on items and how they are ranked on Amazon. The JSON includes a lot of data, but the most important elements are below.

 [
   {
     "timestamp": 1631656500626,
     "products": [
       {
         "title": "This is the very long title of the product",
         "parentAsin": "B086PDNJ98",
         "asin": "B0000D8999",
         ],        
         "salesRankReference": 1055398,
         "salesRanks": {
           "3734871": [
             4828978,
             113,
             4829096,
             124,
             4829320,
             153,
             4829484,
             136,
             4829588,
             106,            
           ]
         }                
       }
     ]
   }
 ]

The problem is that the sales rank listing can be thousands of listings. We would like to map this to a single field in a table and then break it out using stored procedures. When trying to map this field to an NVARCHAR(MAX) field we get an error about the field type not matching.

The given value of type String from the data source cannot be converted to type nvarchar of the specified target column.,Source=System.Data,''Type=System.InvalidOperationException,Message=String or binary data would be truncated.,Source=System.Data,'

Not sure if this is just out of the depth of what ADF can handle.




azure-data-factory
· 2
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.

Hello @TariqJamal-0790,

Welcome to the Microsoft Q&A platform.

I just did a test - Copied a CSV file with a column having about 50000 Characters to copy to SQL Field of NVARCHAR(MAX) via ADF Copy Data. I was able to copy without any issues.

132787-image.png

I just wanted to check with you the activity(like copy data, data flow etc) are you making use for the above action and also the configuration of the same if possible ?

0 Votes 0 ·
image.png (1.5 KiB)

Hi @SathyamoorthyVijayakumar-MSFT

So there is a for each, where we pull the API per ASIN.
Then a copy shape.

Once, no longer include "salesRanks" in the mapping it sends all other fields.

132843-image.png


0 Votes 0 ·
image.png (47.5 KiB)

0 Answers