Azure Data Factory Recursive Query in source option

Question

I'm trying to execute a recursive query in Azure Data Factory Source using the query option.

However, even writting go or ; before the with sentence, Data Factory is not able to project the columns. If I write (before the with clause):

• "Go", I've got this error "shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'r'. If this is intended to be a common table expression, you need to explicitly terminate the previous statement with a semi-colon."
• If I write ";", I've got this one shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ';'.
• If I do not write anything shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'with'.

So I wonder if this kind of code is not allowed in Azure Data Factory, or if there is something I'm missing.

Answer 1

The query that you can enter into the Data Flow's Source Options is extremely limited as it doesn't even support order by clauses. You can reference a user-defined function, however, so you could nest your CTE inside a UDF and then reference that from Source Options.

REF: 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.