CODEWITHSUNDEEP
azure-data-factory
Maus
Maus

Reputation: 122

ADF Mapping Data Flow Source - can query hints (OPTION) be used?

I have a view using a CTE that exceeds the maximum recursion, so I need to select from it using the hint

OPTION (MAXRECURSION 3650)

Is that possible? I do not seem to be able to find any information on it, other than the fact that is not working in the Source Query - any documentation on what you can do as far as SQL queries would be greatly appreciated.

Error message:

at Source 'Calendar': shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'option'.

Source Query:

select * from dbo.ReportingCalendar option (maxrecursion 3650)

The above query is correct and runs on the SQL Server.

Upvotes: 0

Views: 454

Answers (1)

Steve Johnson
Steve Johnson

Reputation: 8660

I refer to this documentation, but there isn't information about keyword 'option'. I also test it with data flow, got the same error with you. So it seems not support this keyword.

As an alternative, you can use copy activity, it supports 'option'. You can copy data from your SQL database to Azure data lake gen2( or someplace else that data flow support as source), then use that as source in data flow and do some transform.

Upvotes: 1

Related Questions