I am working on a SSIS package which checks the performance of stored procedures using extended events. We have an execute SQL task having a SQL query which is determined at run time as the arguments for those (Read) stored procedures are already hard coded and stored in a table.
Now, I need to run (write) stored procedures calls as well for which I can not hard code the arguments as they are (dynamically generated) dependent on output set of the previous stored procedures. Arguments and result set are not identical for each stored procedure and so the difficulty to use a single execute SQL task.
I thought of creating separate execute SQL task for each (write) stored procedure and add them to sequence container but that doesn't seem like a practical approach.
Can somebody suggest any approach to this problem?