Always got same 'OutputDataConversionError.TypeConversionError' , even I remove the datetime column in output in the Synapse DW sql pool, and got same error after delete and recreated stream analystic.

Question

Always got same 'OutputDataConversionError.TypeConversionError' , even I remove the datetime column in output in Synapse DW sql pool, and got same error after delete and recreated stream analystic.

Stream Input is event hub, get dignostic log from azure sql database. Tested pass.
Stream output is a table in azure synapse analystic DW sql pool. Tested ok.
Query is like:
SELECT
Records.ArrayValue.count as [count],
Records.ArrayValue.total as [total],
Records.ArrayValue.minimum as [minimum],
Records.ArrayValue.minimum as [maximum],
Records.ArrayValue.resourceId as [resourceId],
CAST(Records.ArrayValue.time AS datetime) as [time],
Records.ArrayValue.metricName as [metricName],
Records.ArrayValue.timeGrain as [timeGrain],
Records.ArrayValue.average as [average]
INTO
OrderSynapse
FROM
dbhub d
CROSS APPLY GetArrayElements(d.records) AS Records

the query passed the test run. but stream job got into degraded state. and got error:
Source 'dblog' had 1 occurrences of kind 'OutputDataConversionError.TypeConversionError' between processing times '2021-11-12T05:28:08.7922407Z' and '2021-11-12T05:28:08.7922407Z'.

But even I deleted the stream job, drop the [time] column in output table, remove the "CAST(Records.ArrayValue.time AS datetime) as [time], " in the query statement, and recreated a new stream job, still got same error?

Part of the Activity log:
"ErrorCategory": "Diagnostic",
"ErrorCode": "DiagnosticMessage",
"Message": "First Occurred: 11/12/2021 7:39:12 AM | Resource Name: dblog | Message: Source 'dblog' had 1 occurrences of kind 'OutputDataConversionError.TypeConversionError' between processing times '2021-11-12T07:39:12.8681135Z' and '2021-11-12T07:39:12.8681135Z'. ",
"Type": "DiagnosticMessage",

Why? is there a hidden cache I can not clean?

Answer 1

Hello @,
Thanks for the ask and using Microsoft Q&A platform .
As Florian as pointed out this is a bug at this time and ASA team is working on this .

https://stackoverflow.com/questions/69957251/azure-stream-analytic-always-got-same-outputdataconversionerror-typeconversione

"It looks like a bug in the output adapter is provoking that issue. While the fix is rolling out, you can re-order the field list to match the column order in the destination table."

We will keep you update on this , once we hear back from the internal team .

Thanks
Himanshu

It looks like a bug in the output adapter is provoking that issue. While the fix is rolling out, you can re-order the field list to match the column order in the destination table.

Answer 2

I am facing similar issue in Stream Analytics. Has this issue fixed at the output adapter ?