Lately I’ve been working on a deployment process for Integration Services Projects that makes use of the views and stored procedures in SSISDB to deploy folders, variables, ispacs etc etc. The reason why I’ve gone this route instead of any of the others is so that I can just use Sql Data Client dll which is part of .NET. So no extra moving parts required when deploying.

And as part of the deployment I want to run the validate project stored procedure. This is the blurb for what validation does:

Validation helps identify issues that will prevent the packages in the project from running successfully. Use the catalog.validations or catalog.operations views to monitor for validation status.

Only environments that are accessible by the user can be used in the validation. Validation output is sent to the client as a result set.

In this release, project validation does not support dependency validation.

Full validation confirms that all referenced environment variables are found within the referenced environments that were included in the validation. Full validation results list environment references that are not valid and referenced environment variables that could not be found in any of the referenced environments that were included in the validation.

All very handy, let’s put it to good use. And should it fail, I can use the restore project  stored procedure: for those of you not ITK, SSISDB retains versions of previously deployed projects to allow you for rolling back. And here is where things get a little controversial. Firstly, the validation process checks both environment and project. But rolling back only affects the project. Again, the blurb from the MSDN documentation:

When a project is restored, all parameters are assigned default values and all environment references remain unchanged.

So if environment references have been changed for a deployment, then these are not rolled back. This is a problem.

This would be less of a problem if there was a way to roll back environments, but unfortunately this is not the case.  So now what we have to do is, before we make any changes to the environment,

  • backup the currently working environment
  • make our changes
  • validate
  • and if it goes wrong roll everything back

This is not impossible, nor even is it that hard, but it is fiddly and makes the deployment process a bit more convoluted, and possibly prone to errors. Then of course you have to manage those environments that you backed up that you don’t need any more.

There’s another aspect to this: if you have your Integration Services projects in source control, what are you doing rolling back anyway? It would make more sense to just deploy from source control rather than rely on built in project revisions. I have written before about how project history can cause a massive headache for the maintenance job that runs to clear up SSISDB. And finally, if you roll back the environment and project, should you also validate them: maybe someone changed an environment config between deployments unintentionally, or maybe the version is not incorrect form a validation perspective, but has a bug you’re trying to fix?

One of the things I have come to find out about creating a path to live for any technology is that if your deployment process is over-engineered then there’s greater chance that it cause a failure. The rollback feature sound like it;s a good idea, but the reality is is that if you’ve got your code in source control, have builds that kick off on a check-in, versioning the software at the end of your builds and are deploying them regularly, and pushing them to live regularly, then there isn’t really a need for a rollback process.