CODEWITHSUNDEEP
sql-servervisual-studio-2015sql-server-data-toolsdacpac
user875123
user875123

Reputation: 137

Create dacpac files all SQL Server version in single build

We have multiple clients who have different database versions running on their servers. Some clients are using SQL Server 2008, some SQL Server 2012 and some clients are using SQL Server 2014.

I have a SSDT project in my Visual Studio 2015 solution.

For production deployment, first we ask client for the SQL Server version & according to that version, we give the suitable .dacpac file to the client.

Every time on build of SSDT project it only creates a single .dacpac file in the output folder for the selected "Target platform" version (SSDT project > Properties > Project Settings > Target platform)

Is there any way to create multiple .dacpac files for all the version in a single build without changing the "Target Platform" setting manually?

By using pre/post build event OR using some Exe file?

Thanks.

Upvotes: 2

Views: 1873

Answers (2)

SpaceGhost440
SpaceGhost440

Reputation: 579

So, I was trying to figure this out myself, and at first I went down the path of editing the project msbuild xml. Unfortunately I could not get the DSP (SQL Server Version) to change no matter how I approached the build xml. Finally I decided to give up and write a PowerShell that would and create all of the builds. Here is what I came up with in case it helps anyone else. Add this PowerShell to your solution folder, and modify the project path inside of it, and it should create a separate folder per SQL server version for you with each dacpac targeted to that version. Of course all of the build parameters are changeable to fit your desired outputs. I wanted the builds to control not only the SQL version, but the .net framework version as well. You could add other things as well, like conditional compile symbols. Up to you.

Clear-Host

# TIM C: I extrapolated how to do this from the shortcut that installs with VS 2019 called: "Developer PowerShell for VS 2019". 
# I added the take on VSWHERE so it will work for any version 2017+

#. "C:\Program Files (x86)\Microsoft Visual Studio\Installer\vswhere.exe" -?

$instance = (. "$(${env:ProgramFiles(x86)})\Microsoft Visual Studio\Installer\vswhere.exe" -latest -format json | ConvertFrom-Json)

$common7Path = ([System.IO.FileInfo]$instance.productPath).Directory.Parent.FullName
$instanceId = $instance.instanceId
$vsVersion = ([System.Version]$instance.installationVersion).Major

Import-Module "$common7Path\Tools\Microsoft.VisualStudio.DevShell.dll"; 
Enter-VsDevShell $instanceId

$scriptRoot = [System.IO.Directory]::GetParent($MyInvocation.MyCommand.Definition).FullName
Set-Location $scriptRoot
<# *********************************************************************
   EDIT HERE: The sub path to the SSDT project to build
   ********************************************************************* #>
$projectPath = [System.IO.Path]::Combine($scriptRoot, "project\project.sqlproj")
<# *********************************************************************
   ********************************************************************* #>
$projectFileName = [System.IO.Path]::GetFileNameWithoutExtension($projectPath)

$builds = @(
    [pscustomobject]@{sql=2008;version=100;framework="v3.5";constants=""},
    [pscustomobject]@{sql=2012;version=110;framework="v4.5";constants="DOTNET45"},
    [pscustomobject]@{sql=2014;version=120;framework="v4.5";constants="DOTNET45"},
    [pscustomobject]@{sql=2016;version=130;framework="v4.5";constants="DOTNET45"}
)

$configs = @( "Debug", "Release" )

foreach ($build in $builds) {
    foreach ($config in $configs) {
        Write-Host "Building for $($build.sql)-$config" -ForegroundColor Yellow
        & msbuild $projectPath `
            /target:ReBuild /interactive:false /nologo /nodeReuse:false /p:platform="any cpu" /consoleLoggerParameters:ErrorsOnly `
            /p:configuration="$config" /p:VisualStudioVersion="$vsVersion.0" /p:OutputPath="bin\$config\$($build.sql)\" `
            /p:DSP="Microsoft.Data.Tools.Schema.Sql.Sql$($build.version)DatabaseSchemaProvider" `
            /p:TargetFrameworkVersion=$($build.framework) /p:SqlTargetName="$($projectFileName)_$($build.sql)" `
            /p:DefineConstants="$($build.constants)" | Out-Host

        if (!$?) { Write-Host "Build -failed for $($build.sql)-$config" -ForegroundColor Red }
    }
}

Upvotes: 1

Ed Elliott
Ed Elliott

Reputation: 6856

I have had a similar thing where I needed one project to work on multiple versions, the way I managed it was to set it for the lowest version you support (I only needed 2007r2 and 2012 so it wasn't too hard). Then when I deployed I set "AllowIncompatiblePlatform" to true.

Another way would be to open th dacpac as a zip file or using System.Packaging .net api and change the version in the model.xml.

I also blogged about some difficulties you probably already know about when you don't control the database you are deploying to:

https://the.agilesql.club/blogs/Ed-Elliott/2016-09-20/Database-Deployments-Where-A-Customer-Manages-The-Database

If you decide to change the version inside the dacpac let me know and I'll show you how but it does mean you skip the build phase which isn't ideal.

Ed

Upvotes: 1

Related Questions