OutputDataConversionError.TypeConversionError writing to Azure SQL DB using Stream Analytics from IoT Hub

Question

I have wired up a Stream Analytics job to take data from an IoT Hub and write it to Azure SQL Database.

I am running into an issue with one input field which is a date/time object '2019-07-29T01:29:27.6246594Z' which always seems to result in an OutputDataConversionError.TypeConversionError -

[11:59:20 AM] Source 'eventssqldb' had 1 occurrences of kind 'OutputDataConversionError.TypeConversionError' between processing times '2019-07-29T01:59:20.7382451Z' and '2019-07-29T01:59:20.7382451Z'.

Input data sample (sourceeventtime is the problem - other datetime fields also fail).

{
    "eventtype":"gamedata",
    "scoretier":4,
    "aistate":"on",
    "sourceeventtime":"2019-07-28T23:59:24.6826565Z",
    "EventProcessedUtcTime":"2019-07-29T00:13:03.4006256Z",
    "PartitionId":1,
    "EventEnqueuedUtcTime":"2019-07-28T23:59:25.7940000Z",
    "IoTHub":{"MessageId":null,"CorrelationId":null,"ConnectionDeviceId":"testdevice","ConnectionDeviceGenerationId":"636996260331615896","EnqueuedTime":"2019-07-28T23:59:25.7670000Z","StreamId":null}
}

The target field in Azure SQL DB is datetime2 and the incoming value can be converted successfully by Azure SQL DB using a query on the same server.

I've tried a bunch of different techniques including CAST on Stream Analytics, and changing the compatibility level of the Stream Analytics job all to no avail.

Testing the query using a dump of the data in Stream Analytics results in no errors either.

I have the same data writing to Table Storage fine, but need to change to Azure SQL DB to enable shorter automated Power BI refresh cycles.

I have tried multiple Stream Analytics jobs and can recreate each time with Azure SQL DB.

Can this blog stackoverflow.com/questions/56267217/… be helpful? — Leon Yue
I already have my target column set to 'datetime2' and I tested the incoming value as per that post and SQL DB is happy to convert it into a valid datetime2 item. That's why I'm lost! — Simon W

Simon W Simon W · Accepted Answer · 2019-07-29T05:22:08

Turns out that this appears to have been a cached error message being displayed in the Azure Portal.

On further investigation through reviewing detailed logs it appears another value that was too long for the target SQL DB field (i.e. would have been truncated) was the actual source of the failure. Resolving this removed the error.

OutputDataConversionError.TypeConversionError writing to Azure SQL DB using Stream Analytics from IoT Hub

1 Answers