Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

powershell - Deploy Dacpac packages via power shell script to Azure SQL Server

I'm trying to deploy multiple dacpac's during single build process by using PowerShell script.

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:a1s********************deploydatabase.ps1:104 char:13
+ $dacService.Deploy($dp, $database, $true)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : DacServicesException

2017-02-24T06:03:10.0085256Z ##[error]Process completed with exit code 1 and had 1 error(s) written to the error stream.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

First, you need to add firewall rule in order to connect to Azure SQL Server.

  1. Edit your build definition
  2. Select Option tab and check Allow Scripts to Access OAuth Token
  3. 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:

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:

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...