examples/TFGet_MSBuild_SqlPackage.ps1
|
$ReleaseFolder = "\\ITShare\ContosoBI\Releases\1_4_0" $CompareFolder = "\\ITShare\ContosoBI\Releases\1_3_0" # Execute a get latest version using tf.exe (installed with Visual studio 2015) # In most cases, the login including the password in clear text will no be necessary # Check the exit code of the tf.exe execution, terminate the script if it failed # &"C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\TF.EXE" get "$/ContosoBI" /recursive /v:"DEVPC011;CONTOSO\John.Doe" /login:"CONTOSO\John.Doe,P@ssw0rd" if ($LASTEXITCODE -ne 0) { exit } # MSBuild installed with Visual studio 2015 # Set a project file or an entire solution # Set the configuration to use &"C:\Program Files (x86)\MSBuild\14.0\Bin\MSBuild.exe" "C:\Sources\ContosoBI\ContosoBI.sln" /property:Configuration=Development # use exit code from MSBuild.exe to check if build succeeded if ($LASTEXITCODE -ne 0) { exit } # Copy the freshly build SSAS databases to the release folder. # Recursively get all files with extension asdatabase under the solution folder. # get only the files from directories named "bin", so exclude files from obj\x86\Developemnt etc # copy the file to the release folder,using the bin parent folder name (that is the project name) as file name. This should guarentee uniqueness as the files are all named "Model.asdatabase"by default. # e.g. "C:\Sources\ContosoBI\SSAS UK Sales\bin\Model.asdatabase" wil be copied to "\\ITShare\ContosoBI\Releases\1_4_0\SSASTABULAR\SSAS UK Sales.asdatabase" Get-ChildItem "C:\Sources\ContosoBI" -Recurse -Filter "*.asdatabase" | ? {$_.Directory.Name -eq "bin"} ` | ForEach-Object {Copy-Item ($_.FullName) -Destination "$($ReleaseFolder)\SSASTABULAR\$($_.Directory.Parent.Name)$($_.Extension)"} # Copy the built SSIS projects to the release Folder # Recursively get all files with extension ispac under the solution folder. # get only the files from directories named after the build configuration under "bin" Get-ChildItem "C:\Sources\ContosoBI" -Recurse -Filter "*.ispac" | ? {($_.Directory.Name -eq "Development") -and ($_.Directory.Parent.Name -eq "bin")} ` | ForEach-Object {Copy-Item ($_.FullName) -Destination "$($ReleaseFolder)\SSIS\$($_.Name)"} # Copy the Database projects to the release Folder # Recursively get all files with extension dacpac under the solution folder. # get only the files from directories named after the build configuration under "bin". # get only the dacpac files that have the same name as the project directory name, ignoring any referenced dacpac files. Get-ChildItem "C:\Sources\ContosoBI" -Recurse -Filter "*.dacpac" | ? {($_.Directory.Name -eq "Development") -and ($_.Directory.Parent.Name -eq "bin")-and ($_.Directory.Parent.Parent.Name -eq $_.BaseName)} ` | ForEach-Object {Copy-Item ($_.FullName) -Destination "$($ReleaseFolder)\DACPAC\$($_.Name)"} # Loop throug all dacpacs in the release folder # Compare them with the dacpacs of the previous release # using SqlPackage.exe, generate a migration SQL script for each database # If there many dependecies between databases, it is probably better to not use loop like below, but explicitly code all sqlpackage.exe invocations. Get-ChildItem "$($ReleaseFolder)\DACPAC" -Filter "*.dacpac" | ForEach-Object { & "C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\sqlpackage.exe" /ACTION:script /SourceFile:"$($_.FullName)" /TargetDatabaseName:"$($_.BaseName)" /TargetFile:"$($CompareFolder)\DACPAC\$($_.Name)" /OutputPath:"$($ReleaseFolder)\SQL\$($_.BaseName).sql" /p:BlockOnPossibleDataLoss=False /p:DropObjectsNotInSource=True /p:GenerateSmartDefaults=True } |