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.

2 Answers 2


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.

  • I can look into it, but there are cost issues involved. I'm trying to work with what tools I have available... Which is mainly Visual Studio 2015, SQL Server 2014, any features on Windows Server2012 R2, and anything free. (we FINALLY retired our last Windows XP machines last month). Commented Nov 1, 2016 at 18:05
  • I understand, it can sometimes be difficult to secure funding. Having said that, SQL Compare costs £295. How much of your time will it take to roll your own solution, bug fix and maintain it? It would end up costing you way more in the long run than the solution already developed.
    – Andy Jones
    Commented Nov 1, 2016 at 18:23

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.