CODEWITHSUNDEEP
azure-data-factory
etadelta222
etadelta222

Reputation: 11

Azure Data Factory Data Flow source query support for FOR JSON AUTO

I am trying to use below query as source for my data flow but I keep getting errors. Is the fuctionality not supported in data flow?

SELECT  customer.customerid  AS 'customerid',
        customer.customer_fname AS 'fname',    
        customer.customer_lname AS 'lname',
        customer.customer_phone AS 'Phone',
        address.customer_addressid as 'addressid',
        address.Address_type as 'addresstype',  
        address.street1  as 'street1'
FROM customer customer  
   INNER JOIN customer_address address
     ON customer.customerid = address.customerid  
order by customer.customerid
FOR JSON AUTO, ROOT('customer')

I get the following error:

Notifications
Column name needs to be specified in the query, set an alias if using a SQL function

ADF V2, Data Flows, Source

Upvotes: 1

Views: 3551

Answers (1)

Leon Yue
Leon Yue

Reputation: 16431

The error is cause by that Data Flow Query doesn't support order by statement, not the 'FOR JOSN AUTO'.

See the error bellow: enter image description here

Please refence Data Flow Source transformation:

Query: If you select Query in the input field, enter a SQL query for your source. This setting overrides any table that you've chosen in the dataset. Order By clauses aren't supported here, but you can set a full SELECT FROM statement. You can also use user-defined table functions. select * from udfGetData() is a UDF in SQL that returns a table. This query will produce a source table that you can use in your data flow. Using queries is also a great way to reduce rows for testing or for lookups.

SQL Example: Select * from MyTable where customerId > 1000 and customerId < 2000

The query work well in Copy active but false in Data Flow. You need to change the query.

Upvotes: 0

Related Questions