Search code examples
sqlsql-serverazureazure-data-factoryazure-synapse

String To DateTime Error while copying not null date in SQL Server in Azure Data Factory

I am trying to copy data from a CSV to SQL Server. While writing the data I am changing the schema for a column (Source Date (string) --> Target date (date)) in mapping. There is no empty(null) value in the column. I am getting the error:

ErrorCode=SqlOperationFailed,'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 value '24/01/2024' for column name 'EXPORT_DATE' from type 'String' (precision:, scale:) to type 'DateTime' (precision:255, scale:255). Additional info: String was not recognized as a valid DateTime.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.FormatException,Message=String was not recognized as a valid DateTime.,Source=mscorlib,'

Input file looks like: enter image description here

I already tried setting the dateformat as dd/MM/yyyy, ddMMyyyy, dd-MM-yyyy under the mapping section but it still shows the same error. And the output schema in SQL Server side looks:

ID (varchar,null)
TYPE(varchar,null)
TO_DATE(date,null)
FROM_DATE(date,null)
Work(float,null)
EXPORT_DATE(date,null)
Solution

  • enter image description here

    I agree with @allmhuran that the above error is due to the date format of your input data.

    You need to convert your input date format into the correct format which the sink table supports. So, use dataflow for this transformation. First check whether your source and target datasets support dataflow or not. If not use Blob storage as intermediate between your datasets.

    In dataflow, give your source and use derivedColumn transformation. The dataflow also gives null when we use date functions on this string. So, use string operations for this. Here, for sample I am converting the input data to yyyy-MM-dd format. You can convert it to other formats like yyyyMMdd as per your requirement.

    concat(split(mydate,'/')[3],'-',split(mydate,'/')[2],'-',split(mydate,'/')[1])

    enter image description here

    Add your SQL table as sink and execute it from pipeline.

    Here's the expected result:

    enter image description here