Sql-server – SSIS Promote Package – Move Some Stored Procedures

sql serversql server 2014ssisvisual studio 2015

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.

Best Answer

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.