A JSON response looks like this
[
{
"ref": 123456,
"dim_1": "A",
"dim_2": "B",
"peroid": 202601
},
{
"ref": 5E+20242959,
"dim_1": "A",
"dim_2": "B",
"period": 202602
}
]
The second ref (5E+20242959) is a problem - its neither STRING nor an INT.
Ref is defined as NVARCHAR in BU, but as you can see the value is not between “”.
I have tried the following approaches under, still i get the same error
Input string '5E+20242959' is not a valid number. Path '[56935]['ref.53e92a42-b13c-4062-87b4-6bd5a89547b1.1992753']', line 1, position 36741783.How do i solve this?
Here are the approaches i have tried:
Omitting Ref in Table Flattning
In table flattning, i have left it out. I have the other three values, but still landing returns an error:
Defining the value as NULL in table flattning
Field transformation
Condition on column ref in BU
I have defined the following condition on the column, still i get the same error
CASE
WHEN [ref] LIKE '5E+%' THEN NULL
ELSE [ref]
ENDScript Actions
I have created a script that i have run pre-load - still the same error
UPDATE [ODX].[_test]
SET [ref] = NULL
WHERE [ref] LIKE '5E%';
