How To Create Environment-Aware DacPac Deployment Scripts In PowerShell

How To Create Environment-Aware DacPac Deployment Scripts In PowerShell

I was made “aware” of the idea of environment-aware scripts by a colleague of mine a while ago on a project.

Listening to the principles, I wondered why hadn’t I think about this myself before. It sounded so darn basic, yet so masterfully effective. I guess the more I learn about stuff, the more I realize I don’t know anything at all. But that’s a good thing, right? Surely realizing one does not know stuff helps one keep an open-mind? But anyways, less philosophy, more coding.

An environment-aware script is a script that is aware of its environment. Ah, do I hear reflexive definition alarm bells ringing? Or is it my imagination?

No, it is actually that simple. As an example, an environment-aware script may:

  1. Figure out where it is being run upon. Machine name is a likely candidate in a corporate environment as we usually know them before hand.
  2. Decide whether the machine name is an expected target for the script being run.
  3. If so, correctly set itself up for the particularities of that environment and go on to doing its thing.
  4. If not, gracefully (well, with a human readable error) reply “Computer Says No. I don’t know what this machine is and therefore I’m not prepared to go ahead.”.

Environment-aware scripts or processes are all over the place. Take a Windows installation, it’s aware of the properties of the machine it is being installed upon. It will model installation of its binaries depending your hardware. Machines with Intel CPUs will get one set of drivers while AMD machines will get another. But it will still work. However, try to install an x64 release on an x86 system and see how that works. Computer says no. But it says “no” and tells you why, it doesn’t just error out randomly.

This is so ubiquitous, I really wondered why I hadn’t though about this earlier. Kudos to my colleague for reminding me of the basics.

Below is a very basic example of such a script in PowerShell. It will figure out where it is being run at, pick the appropriate configurations and run a deployment of the configured DacPac on the current machine. If the machine is not recognized it will gracefully fail. Well, in this case, it will throw a very red error, so it can get trapped by its caller.

Essentially, you can tag along a script like this with your other deployment files to make your life easier.

    [Parameter(Mandatory = $True)]
    [ValidateSet("Test", "Publish")]
    [string] $Action

# configure the supported environments here
# add a hash table block for each environment
# the script will refuse to work in any other environment
$Environments =
        Name = "ENV1";
        ServerName = "MACHINE1";
        PackageFile = "MyDatabase.dacpac";
        PublishProfile = "MyDatabase.ENV1.publish.xml";
        DeployReportFile = "MyDatabase.ENV1.DeployReport.xml";
        ScriptFile = "MyDatabase.ENV1.Script.sql";
        SqlPackage = "${Env:ProgramFiles(x86)}Microsoft SQL Server110DACbinSqlPackage.exe";
        Name = "ENV2";
        ServerName = "MACHINE2";
        PackageFile = "MyDatabase.dacpac";
        PublishProfile = "MyDatabase.ENV2.publish.xml";
        DeployReportFile = "MyDatabase.ENV2.DeployReport.xml";
        ScriptFile = "MyDatabase.ENV2.Script.sql";
        SqlPackage = "${Env:ProgramFiles(x86)}Microsoft SQL Server110DACbinSqlPackage.exe";

# detect the environment this script is being run on
$ServerName = $Env:COMPUTERNAME

foreach ($Environment in $Environments)
    if ($Environment.ServerName -Eq $ServerName)
        Write-Host "$($Environment.Name) Environment Identified.";

        # locate SqlPackage
        Write-Host "Locating SqlPackage...";
        $SqlPackage = $Environment.SqlPackage;

        # ensure SqlPackage exists
        if (Test-Path $SqlPackage)
            Write-Host "Using SqlPackage @ $SqlPackage";
            throw "SqlPackage not found!";

        if ($Action -Eq "Test")
            Write-Host "Running DeployReport Action...";
            &$SqlPackage "/Action:DeployReport", "/SourceFile:$($Environment.PackageFile)", "/Profile:$($Environment.PublishProfile)", "/OutputPath:$($Environment.DeployReportFile)";

            Write-Host "Running Script Action...";
            &$SqlPackage "/Action:Script", "/SourceFile:$($Environment.PackageFile)", "/Profile:$($Environment.PublishProfile)", "/OutputPath:$($Environment.ScriptFile)";
        elseif ($Action -Eq "Publish")
            Write-Host "Running Publish Action...";
            &$SqlPackage "/Action:Publish", "/SourceFile:$($Environment.PackageFile)", "/Profile:$($Environment.PublishProfile)";
            throw "Action $Action is not supported";

        Write-Host "Action Completed.";
        Write-Host "Exiting...";


throw "Deployment is not supported on this machine ($ServerName)";

If you save this script as, say, DeployStuff.ps1, you’ll have two ways of running this example:

In Test mode, it will compare the DacPac against the database and output the XML DeployReport and the SQL Publish Script onto the same folder. You can check these to ensure the deployment will go the way you want it to go, and no unexpected wizardry will happen.

.DeployStuff -Action Test

In Publish mode, it will publish the DacPac outright, no more questions asked.

.DeployStuff -Action Publish

Output of the entire process goes to the caller (e.g. the PowerShell window here), so remember to adjust this to what best fits your needs.

Jorge Candeias's Picture

About Jorge Candeias

Jorge helps organizations build high-performing solutions on the Microsoft tech stack.

London, United Kingdom