Hello!

I’ve spent a large chunk of the past few months putting together a PowerShell Module that will automate the deployment of SSIS solutions. Recently it went open source, and because I have a poor sense of humour I called it AssistDeploy. Aside from the readme, each function has it’s own documentation in the header. I’ve also written a rather large post on it at my companies blog. All very detailed, go and have a read.

What I’m most pleased about it is how lightweight it is - the functions are only 89kb, and the readme is at 13kb! But more-so than this, I’ve been able to tackle one of my biggest issues - dependencies. The module only requires sqldataclient, which is available in .NET for it to run. So it should run anywhere.

While there are a few features missing (security on the folder for example), one feature i am working on right now is the WhatIf switch. it’s currently only implemented in PublishSsisVariables but this is a major function and probably the best place to start. It’s a bit basic in that it checks if $WhatIf doesn’t exist before it makes a change, but it works.

Another benefit of AssistDeploy is that irespective of whether multiple project share an environment, it is only the environment variables that pertain to a specific project that are interrogated as part of PublishSsisVariables - note the rather awkward looking query that returns the environment variables:

https://gist.github.com/RichieBzzzt/d83abb6ad99f869ccdb2299e1d4c8f4d

This will only return the variables that exist for that project. This is trickier than you’d think!

Somewhat controversially, although I’ve written a rollback process I’ve yet to document it. Where the controversy is really does depend on your point of view - some people crave a rollback process, to the point that they will take a snapshot of a database before  they deploy. And in the case of SSIS there is actually a rollback process. However, this process only rolls back the ispac deployment, not anything else. And because you would only ever want to restore a project to it’s previous version is after validating a project then you’ll need to make all the necessary changes to environment variables also.

Frustratingly, there is no way to restore an environment back to what it was, so you need to manage this yourself. As I’ve mentioned I’ve written the functions to do this. But because there is also the school of thought that you should always fail forward your deployments, a rollback process is unnecessary. To further add to the mire, the aforementioned validate stored procedure is a little…. temperamental. I’ve seen it fail when it shouldn’t, and I’m not a lone in seeing this. Of course, it works. But there are times I’ve seen it not work. And so it’s difficult to say “use this” when I don’t really believe in it myself.

I will eventually document it and get some examples out there, but if it is to be used then it is to be used with caution, owing to the inconsistent nature of the validate process.

I’m keen for people to start trying out AssistDeploy. Give it a go and feedback on the GitHub page!