Azure Data Factory Unable to convert string column value to Datetime

0
votes

I am using a existing ADF pipeline to copy data from a blob to sql. My source data has a column value like '20000101'. I am getting error when ADF try to convert this to dateTime format from string. I am using json translators like "dateTimeFormat": "yyyy-MM-dd HH:mm:ss.fff". But still end up getting error as "Exception occured when converting the value '20000101' cannot convert string to datetime type.

Any suggession?

Note: I have to handle this only through json mapping. Cannot edit the existing pipeline. Any possibilities here?

1
azureazure-data-factory
what's the data format of '20000101',yyyy='2000',01=MM and 01='dd'? – Leon Yue
@LeonYue Yes correct – Anten D
What's the source file, csv or txt? Can you use Data flow? It can achieve that, I will show you the steps. – Leon Yue
@LeonYue This has to be achieved through the copy data. The data source will be a NFS in on prem. So we cannot map the on prem dataset to the dataflow directly. Now for testing purpose data is in blob.Yes the source file is a txt – Anten D
If you can set the NFS in on prem as source in Copy data, Data Flow should also support it as source. Just with Copy data active, we can not achieve that. We need to do the data convert, for example, convert '20000101' to '2000-01-01'. Data Factory can not convert '20000101' to date directly. – Leon Yue

1 Answers

0
votes

No matter the data in blob or NFS in on prem, Data Factory copy active can not convert '20000101' to datetime. Even we set it as DateTime column.

"errorCode": "2200",
    "message": "ErrorCode=TypeConversionFailure,Exception occurred when converting value '20000101' for column name 'dd' from type 'String' (precision:, scale:) to type 'DateTime' (precision:, scale:). Additional info: String was not recognized as a valid DateTime."

To achieve that, we must do some data conversion in data flow With DerivedColumn:

enter image description here

Expression:

toTimestamp(toDate(concat(substring(dd,0, 4), '-',substring(dd,5, 2),'-',substring(dd,7, 2))))

enter image description here

Then it could be mapping to your sink table.