When it comes to transforming structured data, (e.g., applying business logic, standardization etc.) stored in a database, SQL is the most convenient and fit-to-purpose option. Stored procedures provide a way to store the transformation logic as a set of SQL statements that can be re-executed as pre-compiled code.
The Stored Procedure Activity in Data Factory provides and simple and convenient way to execute Stored Procedures. We have discussed Data Factory Stored Procedure Activity briefly in a previous post. This activity enables us to execute the custom stored proc in Azure SQL DB, Synapse Analytics or SQL Server Database (both on-prem or in a cloud VM).
To run a stored procedure on an on-prem SQL Server database, we need to configure a self-hosted integration runtime (SHIR). Once the SHIR has been created, we can create a linked service based on the SHIR.
Another important feature of the stored proc activity is the ability to pass dynamic parameters to the stored proc being executed. (Of course, the stored proc should have parameters defined in the first place.)
To pass dynamic parameters, click on the import button highlighted in the screenshot above, this will bring up any input parameters that have been defined for the stored procedure.
We can now assign dynamic values to the parameters. The supported data types for parameters is specific to the data source being used.
To get an updated list of supported parameter data types, please visit the link below.
https://docs.microsoft.com/en-us/azure/data-factory/transform-data-using-stored-procedure
Azure Data Engineering 