Azure Data Factory, Copy Activity, ODBC Sink Teradata, mapping DateTime

Question

We're implementing a ADF copy activity of Azure Sql Server to on prem Teradata with the ODBC connector. We cant make the mapping work for a DateTime > Timestamp6 column and gets various errors when testing. (due to changing the source format) The source query has been cast/converted to various formats into an intermediate Blob File.

The cast/converts has been:

convert(varchar(26), EventUtcTimeStamp,121) EventUtcTimeStamp //"2020-11-03 12:01:21.928294"
convert(varchar(19), EventUtcTimeStamp,121) EventUtcTimeStamp //"2020-11-03 12:01:21"
cast(DATEDIFF(s, '1970-01-01 00:00:00.000', eventUtcTimeStamp) as bigint) * 1000 eventUtcTimeStamp //1604404881000

The Blob File is the source for the second Copy activity where the target is the teradata table. This generates various ErrorCode=TypeConversionFailure's .

We suspect the cause to be the sink mapping to teradata, since it cant be changed from DateTime. The blob file looks ok.

Any one encountered this and know how to tweak it to work, if possible at all?

Answer 1

Hi @Diring, Peter ,

Thanks for your patience.

I got confirmation from Product team that data factory can recognize date formats as in this document. Today Teradata as a sink is not supported in dataflow yet to convert it as datetime before loading into sink. So, unfortunately, this is not possible without modification at sink today.

I would recommend to submit an idea in feedback forum that is closely monitored by data factory product team and will be implemented in future releases.

Please let us know for further queries and we will be glad to assist.

---

• Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.
• Want a reminder to come back and check responses? Here is how to subscribe to a notification.