Azure Data Factory, Copy Data, Teradata ODBC , dateTime timestamp mapping

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?

Copy Activity

Sink Mapping Teradata

Your first example certainly looks like a timestamp(6). What happens if you try to move it to a varchar column on teradata, what does it look like? — Andrew
Moving it to a varchar column works just fine, its only the timeStamp that fails. — DNG
And just to add how it looks in a teradata varchar column: '2020-03-09 10:27:04.143333' — DNG
Since you applied CONVERT on the source side, shouldn't the sink mapping describe the field in the intermediate Blob File as a string rather than a DateTime? — Fred
What error are you getting when you try to insert that into a timestamp(6) field? @Fred, will Teradata not just do an implicit cast here? — Andrew

DNG DNG · Accepted Answer · 2020-12-10T08:21:42

So, it seems the ODBC sink with Teradata as a target is not fully supported.

We solved this by creating an ODBC Teradata "staging" table with varchar only columns and some stored procedures/triggers to move it into the real columns.

Quoting from here

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.

Azure Data Factory, Copy Data, Teradata ODBC , dateTime timestamp mapping

1 Answers