Use stored procedure at sink with multiple input tables using metadata driven copy task

Question

I have successfully followed the below article to run a stored procedure before my data is stored in the destination database (Azure SQL).
https://learn.microsoft.com/en-us/azure/data-factory/connector-sql-server?tabs=data-factory#invoke-a-stored-procedure-from-a-sql-sink

However I did my test only with one table, while the metadata driven copy task is built for many tables (that is why I use it) and in the sink properties of the copy task I can only specify one stored procedure and related to it the table type and the table type parameter name.

So when I have 2 tables to be processed how can I make the stored procedure dynamic so that it can handle different input tables? Since I have to specify a table type I cannot think of a way on how to make this dynamic so that it works for 2 different table types and in the end also to run the stored procedure depending on the kind of input table!

Answer 1

See this solution https://stackoverflow.com/a/69896947/7392069