I am trying to create an SSIS Package in Visual Studio that I can use when front end deployments are made. Quick rundown -
- copy prod DB to temp backup
- move new/updated stored procedures from prod to test
- run some queries to update prod tables with new version info, error messages, etc.
- 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.