How to run stored procedure in Azure Data Factory DataFlow SQL Server source

Question

When using an Azure SQL Server source, I use the Query option and specify a stored procedure to run. When I paste in the same code in Management Studio, it works, but when executed from ADF source using Query option, it errors with the following condition. How can I call a stored procedure using Query option?

{"message":"at Source 'Source': com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'EXECUTE'.. Details:at Source 'Source': com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'EXECUTE'.","failureType":"UserError","target":"SyncData","errorCode":"DFExecutorUserError"}

Here is the query I'm passing that works when called from SSMS:

EXECUTE [dbo].[sp_ReplicaGetChanges] @ReplicaVersion = 0, @FirstTimeFlag = 1, @SourceSchema = 'dbo', @SourceTable = 'Brand', @UpdateColumns = NULL

Answer 1

For the record I will share my experience just in case someone finds it useful.

TLDR: just type in the schema name and the stored procedure.

Nowadays, in DataFlow sources, one can select "Stored procedure" as a source option (rather than table or query). I was searching how to do it because it was not detecting the stored procedures by default (showing blank dropdowns). Because the dataset is defined at table level, I was afraid it would not work. However, the solution was as simple as typing-in the schema and the procedure names (one gets used to have every option available in dropdowns in ADF). After that, I was able to import the variables used by the procedure as shown in the image

Answer 2

UDFs are supported in Data Flows, but not SPs

https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-sql-database#source-transformation

Answer 3

Some analytics system does not like execute and dbo. Try to change execute to exec and/or remove dbo. From sp E.g. Exec sp_ReplicaGetChanges