Question:
I’m trying to deploy multiple dacpac’s during single build process by using PowerShell script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
param( [string]$publish_profile, [string]$path_to_snapshots, [string]$password ) #Load Microsoft.SqlServer.Dac assembly [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Dac") #Load Dac profile $dacProfile = [Microsoft.SqlServer.Dac.DacProfile]::Load($publish_profile) $dacService = new-object Microsoft.SqlServer.Dac.DacServices($dacProfile.TargetConnectionString) $files = Get-ChildItem "$path_to_snapshots\*.dacpac" foreach ($file in $files) { $fileName = $file.Name Try { $dp = [Microsoft.SqlServer.Dac.DacPackage]::Load($file.FullName) $dacService.Deploy($dp, $database, $true) } } Catch { Write-Host "$fileName deployment has been failed" -foregroundcolor "red" throw $_.Exception; Break } } |
On my local environment everything works great, but during build process on the Visual Studio team services I get an error:
2017-02-24T06:03:09.7955300Z *********.dacpac deployment has been failed
2017-02-24T06:03:09.9785258Z ##[error]Exception calling “Deploy” with “3” argument(s): “Could not deploy package.”
At D:\a\1\s********************\deploydatabase.ps1:104 char:13
+ $dacService.Deploy($dp, $database, $true)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : DacServicesException2017-02-24T06:03:10.0085256Z ##[error]Process completed with exit code 1 and had 1 error(s) written to the error stream.
Answer:
First, you need to add firewall rule in order to connect to Azure SQL Server.
- Edit your build definition
- Select Option tab and check Allow Scripts to Access OAuth Token
- Add Azure PowerShell step (arguments: -RestAddress https://[account].vsdtl.visualstudio.com/DefaultCollection/_apis/vslabs/ipaddress -Token $(System.AccessToken) -RG [resource group] -Server [server name] -ruleName $(Build.BuildNumber)
Code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
param ( [string]$RestAddress, [string]$Token, [string]$RG, [string]$Server ) $basicAuth = ("{0}:{1}" -f 'test',$Token) $basicAuth = [System.Text.Encoding]::UTF8.GetBytes($basicAuth) $basicAuth = [System.Convert]::ToBase64String($basicAuth) $headers = @{Authorization=("Basic {0}" -f $basicAuth)} $result = Invoke-RestMethod -Uri $RestAddress -headers $headers -Method Get Write-Host $result.value New-AzureRmSqlServerFirewallRule -ResourceGroupName $RG -ServerName $Server -FirewallRuleName "UnitTestRule" -StartIpAddress "$($result.value)" -EndIpAddress "$($result.value)" |
Secondly, I recommend you use the assembly in this package:
Microsoft.SqlServer.Dac.
Thirdly, to get the detail error, you can use this code instead:
|
1 2 3 4 5 6 7 8 |
Catch { Write-Host "$fileName deployment has been failed" -foregroundcolor "red" $Error | format-list -force Write-Host $Error[0].Exception.ParentContainsErrorRecordException; Break } |
On the other hand, I recommend you can deploy SQL package through SqlPackage.exe.