I’m importing multiple addressbase CSV files that don’t have headers via Azure Synapse and ADLS so using MAPPING_JSON in an ETL.Process table with SQL Server Management Studio 20.1.10.0.

That part is fine but I’m struggling to convert the blank cells into NULLS (to save me amending matching code for addresses on other systems) which I’m hoping to do via the JSON at import. Few hours googling and countless attempts which all run but I’m not sure if I’ve got the null clause in the right place or whether the ‘column’ part needs changing as they still produce blank cells instead of nulls…solution would be much appreciated, example attached

{
“type”: “TabularTranslator”,
“mappings”: [
{
“source”: {
“type”: “String”,
“ordinal”: 1
},
“sink”: {
“name”: “UPRN”,
“physicalType”:”iif(toString(length(rtrim(Column))) > 0, Column,toString(null()))”
}
},
{
“source”: {
“type”: “String”,
“ordinal”: 2
},
“sink”: {
“name”: “OS_ADDRESS_TOID”,
“physicalType”:”iif(toString(length(rtrim(Column))) > 0, Column,toString(null()))”
}
}
],
“typeConversion”: true,
“typeConversionSettings”: {
“allowDataTruncation”: true,
“treatBooleanAsNumber”: false
}
}