In my project I receive data from Azure IoThub and want to send it to a SQL database using Azure stream analytics. I'm trying to achieve this using the following query:
SELECT
IoTDataArrayElement.ArrayValue.sProjectID AS id
INTO
[test-machine]
FROM
[iothub-input] AS e
CROSS APPLY GetArrayElements(e.iotdata) AS IoTDataArrayElement
HAVING IoTDataArrayElement.ArrayValue IS NOT NULL
When I run the query in the environment provided by stream analytics and press test query I get the expected output which is a projectID. But when I start the stream analytics job the data doesn't go in to my database table. The table has 1 column 'id'.
When I try to send all the data to a blob storage the stream analytics job works.
Can someone please explain to me why the query I use for sending the data to a database doesn't actually send the data to a database?
Couple of things you need to verify to make successfully configuration of Azure SQL DB as output:
The following table lists the property names and their description for creating a SQL Database output.
Hope this helps.