We got the below while trying to UPSERT contact records from a SQL table to Dynamics 365 CE using Kingswaysoft Dynamics 365 Adapter.
The source table had the Date of birth (DOB) as nvarchar type (string) and one of the rows had the following value stored for DOB à
‘0201-01-09 00:00:00’
Which was resulting into exception while creating record inside Dynamics 365 CE with the above date.
The way we fixed it was to make use of
Derived Column
à Converting empty string to null for DOB column. https://nishantrana.me/2018/09/10/cannot-convert-between-unicode-and-non-unicode-string-data-types-error-in-ssis/
Data Conversion
à Converting it to Date data type for comparison.
Conditional Split
à Updating DOB field in CRM for only those records having valid date i.e. greater 01/01/1753 00:00:00 and for records having null or date less than 01/01/1753 00:00:00, removing or not using the DOB field for Upsert.
Derived Column à
Data Conversion à Convert it to Date Type which makes it easy for use in Conditional Split Transformation editor for comparison.
Conditional Split à If the DOB is not null and is greater than 01/01/1753
!ISNULL([Copy of DOB]) && [Copy of DOB] >= (DT_DBDATE)”01/01/1753 00:00:00″
Then update the DOB for the record for Case 1 and in case of the DOB being less than 01/01/1753 and null, do not update the field (don’t map the field for update).
Inside Dynamics CRM Destination component
For Case 1: Map the birthdate field
For default output: Don’t map the field, which will set the field as null as required.
Hope it helps..
Nishant Rana's Weblog 