Recently while writing an SSIS package, we were getting the below exception.
System.Exception: Error: Failed to set field ‘birthdate’ value to ” for entity ‘contact’ (field type: DateTime, value type: String). System.FormatException: String was not recognized as a valid DateTime. (SSIS Integration Toolkit for Microsoft Dynamics 365, v10.2.0.6972 – DtsDebugHost, v14.0.3025.39)
We were getting the exception because the source table was having the birth date field as a string with blank value instead of null and in the destination table we had the corresponding mapped field as DateTime type.
The fix is to add a Derived Column with the following expression, which converts the blank string to null à
“TRIM(ColumnName) == “” ? (DT_STR,4,1252)NULL(DT_STR,4,1252) : Columnname”
Hope it helps..
Nishant Rana's Weblog 
3 thoughts on “How to – convert a blank string to Null for DateTime field – SSIS”