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.

woodymoo 256 Reputation points
2021-11-12T10:31:09.177+00:00

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?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,328 questions
Azure Stream Analytics
Azure Stream Analytics
An Azure real-time analytics service designed for mission-critical workloads.
330 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HimanshuSinha-msft 19,376 Reputation points Microsoft Employee
    2021-11-15T22:51:46.033+00:00

    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.

  2. Karthik Thota 1 Reputation point
    2022-04-14T18:51:27.773+00:00

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