Azure Data Factory: Wrong and unexpected Datatype conversion error during import from csv to sql server via pipeline

Question

I am trying to load data from a csv to a SQL server database using an Azure pipeline copy data operator.It throws following error during trigger the pipeline. In CSV file i have one date column (StatusDate) with null values when i am importing data in MS-SQL Data base table is having StatusDate is a date time column.

Error msg[![Source CSV File: "Operation on target Copy data1 failed: Failure happened on 'Source' side. 'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=Microsoft.Azure.Data.Governance.Plugins.Core.TypeConversionException,Message=exception occurred when converting data from column name 'StatusDate', type 'String', value 'null' to type 'DateTime': The string was not recognized as a valid DateTime. There is an unknown word starting at index 0.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.FormatException,Message=The string was not recognized as a valid DateTime. There is an unknown word starting at index 0.,Source=mscorlib,'"

Destination Table Structure:

create table RepsNoLongerInDiscovery(
[CRD]   [varchar](11)   ,
[First] [varchar](50)   ,
[Middle]    [varchar](50)   ,
[Last]  [varchar](50)   ,
[Suffix]    [varchar](50)   ,
[Status]    [varchar](20)   ,
[StatusDate]    [datetime]  ) 

Answer 1

I tested with copy active and it works well.

My dataset:

Bellow is my Source:

Note: we must specify the column data type as DateTime in Mapping settings:

Then run the pipeline and check the data in Sink table:

Hope this helps.