Monday, March 23, 2015

SQL and SSIS Deployment/Execution PowerShell Modules

We've done a bunch of work to leverage PowerShell to completely automate our SQL Server and SSIS deployments.  This includes the management of ISPAC and DACPAC packages respectively.  I'd like to share this work with everyone at my blog repo, and specifically here.

I must note the overall approach is a DBA-centric one.  The modules make heavy use of SSISDB and the other packaging executables.  We did this instead of using the C# centric API.  The largest problem with not using that API is that when things change, the modules will have to change. The trade-off is now we have something that can be expanded upon by savvy DBAs, which in my opinion is the audience for these modules.

The modules can be had under the Attribution-ShareAlike Create Commons license.  Please let me know if you are having success with the modules.  So now onto the good part, what's included?!?

SqlDeploy.psm1 - a few common functions leverages by all the modules
DacPacDeploy.psm1 - all things DACPAC.  Includes drift reporting and creating sql scripts.
IsPacDeploy.psm1 - all things ISPAC.  Deploy, create environments, map variables etc.
SSISPS.psm1 - this controls package execution.

I hope these modules can help people out, send a comment if you find them useful!