通过 Power Shell 脚本将 Dacpac 包部署到 Azure SQL Server [英] Deploy Dacpac packages via power shell script to Azure SQL Server

问题描述

我正在尝试使用 PowerShell 脚本在单个构建过程中部署多个 dacpac.

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
    }
}

在我的本地环境中一切正常，但在 Visual Studio 团队服务的构建过程中，我收到一个错误:

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 部署失败
2017-02-24T06:03:09.9785258Z ##[错误] 使用3"个参数调用部署"的异常:无法部署包."
在 D:a1s********************deploydatabase.ps1:104 char:13
+ $dacService.Deploy($dp, $database, $true)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], ParentContainsErrorRecordException
+ FullQualifiedErrorId : DacServicesException

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]进程已完成，退出代码为 1，并将 1 个错误写入错误流.

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

推荐答案

首先，您需要添加防火墙规则才能连接到 Azure SQL Server.

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

1. 编辑您的构建定义
2. 选择选项选项卡并选中允许脚本访问 OAuth 令牌
3. 添加 Azure PowerShell 步骤(参数:-RestAddress https://[account].vsdtl.visualstudio.com/DefaultCollection/_apis/vslabs/ipaddress -Token $(System.AccessToken) -RG [资源组] -Server [服务器名称] -ruleName $(Build.BuildNumber)

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)

代码:

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)"        

其次，我建议你使用这个包中的程序集:Microsoft.SqlServer.Dac.

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
    }

另一方面，我建议您可以通过 SqlPackage.exe.

On the other hand, I recommend you can deploy SQL package through SqlPackage.exe.

这篇关于通过 Power Shell 脚本将 Dacpac 包部署到 Azure SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！