Sink seems to omit automatically all primitive types, if they are null, I need the same for a complex type objects, when all their attributes are null, because currently I end up with state "complexType": {} , so I need to omit that empty object somehow.
In a Derived Column I tried using an expression like:
iif(!isNull(complexType.field) && !isNull(complexType.field1) && !isNull(complexType.field2),
complexType, null)
but that doesn't seem to work.
Expression should return the same type 'null' as previous expressions.
The above error occurs in ADF dataflow, when expressions in iif are of different types. If one expression is of complex type and other is of null type, it is not possible to update the value. Also, you cannot convert the null value to complex datatype. Only way to replace {} empty object is to convert the expressions in iif to string type. Below is the correct expression.
iif(isNull(complexfield.field1)&&isNull(complexfield.field2),toString(null()),toString(complexfield))
This expression checks if the field1 and field2 fields of the complexfield object are null, and if they are, it returns a null value using toString(null()). If the fields are not null, it returns the complexfield object as a string using toString(complexfield).