SSIS Promote Package - Move Some Stored Procedures

Question

I am trying to create an SSIS Package in Visual Studio that I can use when front end deployments are made. Quick rundown -

1. copy prod DB to temp backup
2. move new/updated stored procedures from prod to test
3. run some queries to update prod tables with new version info, error messages, etc.
4. validate
5. drop temp backup if validation passes.

I'm stuck on #2... Is there a way I can compare PROD and TEST stored procedures for differences and update/add to PROD if there is a difference. I don't want to have to add a SQL Task for each stored procedure for each deployment. I'd also prefer to not wipe all stored procedures and replace them all...

This brings me to a related question, I have another SSIS package (runs nightly) that wipes TEST, recreates TEST from PROD, runs any table changes, then I manually add SQL tasks to the package for new/updated stored procedures one by one... is there a better way to go about it than manually adding each SP through a SQL task?

In both cases I'd like to be able to automatically identify and resolve the discrepancies of stored procedures between databases and resolve without having to manually add SQL Tasks for each new/altered procedure.

Answer 1

Have you looked at a third party tool such as SQL Compare from Redgate? This is designed to compare schemas and ease the process of deploying changes.

I don't think SSIS is the most appropriate tool for this task.

Answer 2

The answer was staring right in front of me the entire time... you can do for/each loops in the SSIS package! I put my stored procedures in a directory for the current release, then set up a for/each loop to execute each .sql file in the directory.