Synchronization Editor, Native Database Connector, SQL-Server provisioning NULL values

Hi Olivier,
I can not verify your observation with 8.1. What version are you using? There has been some changes how the NULL values are handled.

Hi Tomi,

we use version 7.1.4 but the problem exists in version 8.1 as well.

what we figured out is, that the problem exist if you use Data Type varchar or nvarchar on the SQL-Server, then 1IM replaces NULL values with "string empty" values,

If you use for ex. Data Type datetime or text then it is possible to write NULL values.

I did some background research. The problem is that OneIM doesn't differ with null and default value of the datatype. If the property is a reference then One Identity Manager is able to differ and write null values.
Workaround: If there are only few properties where the difference between empty and null are important, one could write a script for the property in the mapping. 

Thank you for your research

there are mainly 3 properties which I have to set in some cases to NULL and that without changing the datatype. (varchar)

until now I'm not able to change the property by script 

I tryed "System.DbNull.Value"  but then I had to add .ToString and ended with a "string empty" again instead of NULL...

You are right that even the script property doesn't help. It seems that the problem can't be solved with the mapping. One option would be to define script based data operations. These can be found when the target system connection is edited on the page "define data operations".

Other workaround: Adding a condition to the mapping rule (disabling mapping rule if value is null on your side) - this might cause issues when you're updating data from "something" to NULL.

You are right that even the script property doesn't help. It seems that the problem can't be solved with the mapping. One option would be to define script based data operations. These can be found when the target system connection is edited on the page "define data operations".

Other workaround: Adding a condition to the mapping rule (disabling mapping rule if value is null on your side) - this might cause issues when you're updating data from "something" to NULL.

Thank you for the answer

i have already a condition to the mapping rule in place, so i have only the case updating from "something" to NULL.

I will now try with the script based data operation, thanks for that hint!

I could fix it with this script based data operation:

Dim store As IValueStore = data.Values.CreateValueStore()
Dim sVisum as String = data.SystemObject.getValue("sAMAccountname","").ToString
Dim sPersonalTitleGerman as String = store.getValue("personalTitleGerman","")

' Check if Attribute was changed. The IValueStore contains only the changed columns.
' Use data.SystemObject.GetValue to fetch values from unchanged columns of the object

If store.Contains("personalTitleGerman") Then
If String.IsNullOrEmpty(sPersonalTitleGerman) Then
connection.ExecuteSqlNonQuery(store.Replace(String.Format("UPDATE Tel_Daten SET personalTitleGerman = NULL WHERE sAMAccountname = '{0}'",sVisum), True))
Else
connection.ExecuteSqlNonQuery(store.Replace(String.Format("UPDATE Tel_Daten SET personalTitleGerman = '" & sPersonalTitleGerman & "' WHERE sAMAccountname = '{0}'",sVisum), True))
End If
End If

Thanks for sharing your solution. Much appreciated.

You should reconsider the way your queries are build. Your example is vulnerable to sql injection.
Currently provisioning a value like "foo'--" as personalTitleGerman will result in all entries in Tel_Daten being updated to personalTitleGerman = foo.