SSIS Tutorial Part 112-Execute Multiple Stored Procedures from a Table by using Execute SQL Task

In this video we will learn How to Execute list of Stored Procedures those names are saved in a SQL Server Table.

The video will cover following items

How to store Stored Procedure names with Schema in SQL Server Table
How to Read the List of Stored Procedures from SQL Server Table by using Execute SQL Task and save them into Object Type Variable
How to Loop through List of Stored Procedures By using Foreach Loop Container
Save values from Foreach Loop Container to variables
Execute Stored Procedure by using Execute SQL Task

Scenario:

Think about a situation where your developers has special requirement and they want to run stored procedure from a table and they have capability to update/insert the table records. Or there is possibility that you want to have the flexibility to run more or less Stored Procedure on daily basis without changing the SQL Server Agent Job or SSIS Package.

Blog post link for the video with scripts

Check out step by Step SQL Server Integration Services(SSIS) Tutorial by TechBrothers

[krismaly]

I enjoyed watching this video.
Thanks for educating the community

[zoyasaleem]

hi can you please explain for each container run the procedure parallel or one by one ? means if one procedure have error then other will not work.

[larryding]

I think I'm kinda late here, but I want to know what if I have some SPs with different input parameters, how can I do that, I mean execute them at one time, also use a loop. thx

[anujtripathi]

Though, this is a unique requirement but I would never suggest this architecture. This is quite bad approach and will lead down SQL Server performance. 

About your video: Nice work but one advice. Do not use EXEC.. it is a bad practice, instead use sp_executesql.