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.

AlwaysLearning · Accepted Answer · 2020-11-17 03:36:57Z

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.

answered Nov 17, 2020 at 3:36

AlwaysLearning

8,9525 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

d2907 Over a year ago

This is a shame. It forces to create a new object in the database, something we were trying to avoid. It should be considered a possible improvement in Azure Data Factory.

Collectives™ on Stack Overflow

Azure Data Factory Recursive Query in source option

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related