Copy Data Tool JSON to CDS -- Nulling an existing CDS column without updating entire record
We are attempting to perform bulk copy operations of data from a JSON object to the Common Data Service For Apps. We#re using a Pipeline and the Copy Data Tool.
However, we have an issue passing updates that include Nulling a value.
We are using Dynamic Data Mapping, for each table we simply hold the mapping of the JSON name to the CRM name and it's respective type. The Map includes all columns that can be mapped from the JSON file to the CDS.
These are all upsert operations. Our challenge is:
We only pass the columns that have changed on the object within the JSON file, we might be changing an Int32 column from "21" to null.
An update might look like this:
{
"Id": "E61A1F1E-5F93-4BE5-B458-E885E210C261",
"eg_name": "adfTest",
"eg_version": null
}
And, for the sake of argument our mapping file will look something like this
{
"type": "TabularTranslator",
"mappings": [
{
"source": {
"path": "$['Id']"
},
"sink": {
"name": "eg_egid",
"type": "Guid"
}
},
{
"source": {
"path": "$['eg_name']"
},
"sink": {
"name": "eg_name",
"type": "String"
}
},
{
"source": {
"path": "$['statuscode']"
},
"sink": {
"name": "statuscode",
"type": "Int32"
}
},
{
"source": {
"path": "$['eg_version']"
},
"sink": {
"name": "eg_version",
"type": "Int32"
}
}
]
}
Without setting "Ignore Null Values" we will get an error on the upsert stating:
ErrorCode=DynamicsOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Dynamics operation failed with error code: -2147220989, error message: Attribute: statecode cannot be set to NULL
So it seems as if simply by having statecode in our mapping means that the Copy Data Function expects a value to be there for it, and if there isn't one will just set it to Null..
Ok.. So, if we set "Ignore Null Values" checkbox -- the Upsert is partially succesfull, eg_name has been updated but eg_version remains as it's previous value seeing as we told the copy data thing to Ignore Null Values.
So my question is: How can we pass null values for our columns in ADF without receiving an error stating we are trying to set columns that aren't included in this change to Null?