I can see that when an ETL tool is using the Bulk API, there isn't a global "insert null values" setting and you have to explicitly set cells to
#N/A to get them to overwrite to null.
That's nice, because in my situation, I am using the tool to data-load tabular data where I will want to distinguish, cell-by-cell, between these two meanings:
- "I have nothing new to add -- whatever is already in Salesforce is fine"
- "Please actually clear out this value in Salesforce"
The question is, am I stuck using the Bulk API if I want to use an ETL tool that simply offers an operation-wide "INSERT null values" checkbox on "non-bulk" data loads?
It looks like perhaps tools could be built to have "field whitelists" where any
nulls should be automatically interpreted as "please actually clear out this value in Salesforce" on a field-by-field basis (which in this case is granular enough for me ... for certain fields, the data being loaded will always be the "source of truth" so universal load-nulls for those fields is acceptable), but I don't see a UI in my tools (particularly Jitterbit) to actually take advantage of that.
Am I likely stuck refactoring my standard UPSERT in a 3rd-party ETL tool into 2 separate operations (one for the "insert nulls checked" columns, one for the rest) / refactoring it into a bulk operation (unfortunately, Jitterbit doesn't just let you toggle a "use bulk" switch, so that doesn't sound fun either)?