Couldn't figure out how can we use a stored procedure as source dataset in Azure Data Factory copy activity? Is there a way to have a stored procedure as the source data in a copy data task?
0
votes
2 Answers
1
votes
Yes, ADF supports to read data from a stored procedure in Copy activity. See the below picture, we use an Azure SQL dataset as example, click the stored Procedure checkbox, select the stored procedure script in your database then fill the parameter if needed. This doc provides more information. Thanks.
![[1]: https://i.stack.imgur.com/iE5dp.png](https://i.stack.imgur.com/56KdD.png)
1
votes
Be careful as when using the stored procedure source with 'auto create' table set a schema infer step is performed which executes the code in the stored procedure in a VERY peculiar way that can cause pipeline errors - especially if you have any dynamic SQL and/or conditions in the stored procedure code! There is a work-around I have discovered which allows the stored procedure to be written in a fully functional way without breaking the pipeline. I will write a separate article on it perhaps.
