通过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 ## [error]使用"3"个参数调用"Deploy"的异常:无法部署程序包."
在D:\ a \ 1 \ s ******************** \ deploydatabase.ps1:104 char:13
+ $ dacService.Deploy($ dp，$ database，$ true)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo:未指定:(:) []，ParentContainsErrorRecordException
+ FullyQualifiedErrorId: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:\a\1\s********************\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://[帐户] .vsdtl.visualstudio.com/DefaultCollection/_apis/vslabs/ipaddress -令牌$(System.AccessToken)-RG [资源组]-服务器[服务器名称] -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
    }

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

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

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