0

I have an SSDT database project in Visual Studio 2013. This is used as the "answer sheet" when publishing database updates to a database in the other environments. I recently came across Jamie Thompson's blog article on DacPacs, where he writes a great summary on what DacPacs are, and how to use them.

Now, say I have the following scenario:

  1. The SSDT project in VS2013, which is version 1.0.33
  2. A database in my Dev environment, which is version 1.0.32
  3. A database in my S-test environment, whic is version 1.0.31

According to Jamie, publishing databases changes using DacPacs is idempotent, i.e. I can publish the DacPac from the SSDT project in bullet 1 to the database in bullet 3, and it will get all the changes done to the database in both version 1.0.32 and 1.033 since the DacPac contains information about the entire DB schema (which then also should include changes done in version 1.0.32).

Is this a correct understanding of how publishing a DacPac works?

nils1k
  • 467
  • 5
  • 20
  • Yes it's right. And you can deploy your dacpac from command line too using SqlPackage.exe – lucazav Jul 10 '15 at 07:00
  • Excellent. Thnx for confirming, lucazav. Btw, do you know if there are any limitations with regards to how publishing handles data updates in the DB, e.g. if I add a new row in a lookup table? – nils1k Jul 10 '15 at 07:54
  • hey nils - take a look at this for how to handle lookup data https://the.agilesql.club/Blog/Ed-Elliott/AdventureWorksCI-Step3-Handling-Data-With-SSDT – Ed Elliott Jul 10 '15 at 08:19
  • Hey Ed. Thnx a lot - I'll give your blog series a read. – nils1k Jul 10 '15 at 08:31
  • I had the same link under my mouse. +1 to @EdElliott for his speed! :) nils, if you want I can answer your question, posting a PowerShell script that helps you to publish your dacpac from command line. Just let me know. – lucazav Jul 10 '15 at 08:45
  • 1
    cheers guys! Luca do it or blog it! – Ed Elliott Jul 10 '15 at 08:52
  • I had already created a script for publishing the dacpac before asking the question, @lucazav. thanks, though :). – nils1k Jul 10 '15 at 10:19

1 Answers1

2

Yes, once you defined your model in a DACPAC in a declarative way, you can then deploy your model to any target environment with whatever version of you database. The engine will automatically generate the proper change scripts according to the target.

You can deploy (publish) your model from Visual Studio or from command line using the SqlPackage.exe utility. Here an example of a PowerShell script that use SqlPackage.exe and a Publish Profile file. You can choose to publish directly or generate the change script (set the $action variable). The DACPAC file and the Publish Profile file have to be in the same folder of the ps file. A log file will be generated:

$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition

####################################
$action                 = 'Publish' #Only generate script: 'Script'; Publish directly: 'Publish'

$databaseName       = 'Test'
$serverName         = 'localhost'
$dacpacPath         = Join-Path $scriptPath '.\Test\bin\Debug\Test.dacpac'
$publishProfilePath = Join-Path $scriptPath '.\Test\Scripts\Publish\Test.publish.xml'


$outputChangeScriptPath = Join-Path $scriptPath 'TestDeploymentScript.sql'

$logPath = Join-Path $scriptPath 'TestDeployment.log'
####################################



$sqlPackageExe = 'C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe'

if ($action.ToUpper() -eq 'SCRIPT')
{

    Write-Host '********************************' | Tee-Object -File "$logPath"
    Write-Host '*  Database Objects Scripting  *' | Tee-Object -File "$logPath"
    Write-Host '********************************' | Tee-Object -File "$logPath"

    $args = "/Action:Script /TargetDatabaseName:$databaseName /TargetServerName:$serverName " +
            "/SourceFile:""$dacpacPath"" /Profile:""$publishProfilePath"" /OutputPath:""$outputChangeScriptPath"" "

    $command = "& ""{0}"" {1}" -F $sqlPackageExe, $args

    Invoke-Expression $command | Tee-Object -File "$logPath"

    if($LASTEXITCODE -ne 0)
    {
        $commandExitCode = $LASTEXITCODE 
        $Error[0] | Tee-Object -File $outputChangeScriptPath
        return $commandExitCode
    }

}

if ($action.ToUpper() -eq 'PUBLISH')
{
    # DWH
    Write-Host '*********************************' | Tee-Object -File "$logPath"
    Write-Host '*  Database Objects Deployment  *' | Tee-Object -File "$logPath"
    Write-Host '*********************************' | Tee-Object -File "$logPath"

    $args = "/Action:Publish /TargetDatabaseName:$databaseName /TargetServerName:$serverName " +
            "/SourceFile:""$dacpacPath"" /Profile:""$publishProfilePath"" "

    $command = "& ""{0}"" {1}" -F $sqlPackageExe, $args

    Invoke-Expression $command | Tee-Object -File "$logPath"

    if($LASTEXITCODE -ne 0)
    {
        $commandExitCode = $LASTEXITCODE 
        $Error[0] | Tee-Object -File $outputChangeScriptPath
        return $commandExitCode
    }
}
lucazav
  • 858
  • 9
  • 24
  • I like the powershell script. I just used batch files to accomplish the same (old-school), but this is a bit more robust and can handle the errors a little better. – Peter Schott Jul 13 '15 at 16:45