Stream Analytics doesnt output the data to sql but does to a blob storage

Question

In my project i recieve data from Azure IoThub and want to send it to a SQL database using Azure stream analytics. Im 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 enviorment provided by stream analytics and press test query i get the expected output wich is a projectID. But when i start the stream analytics job the data doesnt 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 doesnt actually send the data to a database?

is the column type in the database compatible? Does the db firewall allow connections from ASA? — silent
@silent Thanks for the comment, The db does allow connections from ASA and the columnh type in the database is compatible — Collin

CHEEKATLAPRADEEP-MSFT CHEEKATLAPRADEEP-MSFT · Accepted Answer · 2019-10-22T09:39:22

Couple of things you need to verify to make successfully configuration of Azure SQL DB as output:

Make sure firewall settings is ON for All Azure Services.

Make sure you have configured the output to the sql database with the correct properties defined.

The following table lists the property names and their description for creating a SQL Database output.

Make sure the table schema must exactly match the fields and their types in your job's output.

Hope this helps.

Stream Analytics doesnt output the data to sql but does to a blob storage

1 Answers