如何在Azure DevOps CI/CD管道中运行Synapse工作区的Create SQL脚本？

方案一：直接在YAML管道中扩展步骤 #

你可以基于现有的脚本查找逻辑，添加PowerShell步骤逐个执行找到的SQL脚本。这里使用Invoke-SqlCmd2（需确保代理已安装SqlServer模块，或在步骤中提前安装），同时配置Synapse工作区的连接信息（服务器名、数据库名、认证方式等）。

完整YAML片段如下：

# 若代理未预装SqlServer模块，先执行安装步骤
- task: PowerShell@2
  displayName: 'Install SqlServer Module'
  inputs:
    targetType: 'inline'
    script: |
      Install-Module -Name SqlServer -Force -AllowClobber -Scope CurrentUser

# 查找并执行所有Create开头的SQL脚本
- task: PowerShell@2
  displayName: 'Execute Create SQL Scripts'
  inputs:
    targetType: 'inline'
    script: |
      # 配置Synapse连接参数
      $synapseServer = "<你的Synapse工作区服务器名>.sql.azuresynapse.net"
      $databaseName = "<目标数据库名>"
      
      # 用服务主体认证（建议将敏感信息存在Azure DevOps变量组中）
      $secureSecret = ConvertTo-SecureString $(ServicePrincipalSecret) -AsPlainText -Force
      $credential = New-Object System.Management.Automation.PSCredential ($(ServicePrincipalId), $secureSecret)

      # 定位目标脚本
      $scripts = Get-ChildItem -Path "syn-ws-${{ parameters.environment }}" -Filter "Create*.sql" -Recurse -File

      if ($scripts.Count -eq 0) {
          Write-Host "No Create*.sql scripts found."
          exit 0
      }

      Write-Host "Found $($scripts.Count) SQL scripts to execute:"
      $scripts | ForEach-Object { Write-Host $_.FullName }

      # 逐个执行脚本
      foreach ($script in $scripts) {
          Write-Host "Executing script: $($script.FullName)"
          Invoke-SqlCmd2 -ServerInstance $synapseServer -Database $databaseName -Credential $credential -InputFile $script.FullName
          Write-Host "Successfully executed $($script.FullName)"
      }
  env:
    ServicePrincipalId: $(ServicePrincipalId)
    ServicePrincipalSecret: $(ServicePrincipalSecret)

注意事项： #

• 替换<你的Synapse工作区服务器名>、<目标数据库名>为实际值
• 敏感信息（服务主体ID、密钥）需存储在Azure DevOps变量组/秘密变量中，禁止硬编码
• 若代理已预装SqlServer模块，可跳过安装步骤

方案二：使用独立PowerShell脚本封装逻辑 #

如果你偏好将执行逻辑封装到单独脚本，可创建如下PowerShell文件（命名为Run-SynapseCreateScripts.ps1）：

param(
    [Parameter(Mandatory=$true)]
    [string]$EnvironmentPath,
    [Parameter(Mandatory=$true)]
    [string]$SynapseServer,
    [Parameter(Mandatory=$true)]
    [string]$DatabaseName,
    [Parameter(Mandatory=$true)]
    [string]$ServicePrincipalId,
    [Parameter(Mandatory=$true)]
    [string]$ServicePrincipalSecret
)

# 安装SqlServer模块（按需执行）
if (-not (Get-Module -Name SqlServer -ListAvailable)) {
    Install-Module -Name SqlServer -Force -AllowClobber -Scope CurrentUser
}

# 创建服务主体凭据
$securePassword = ConvertTo-SecureString $ServicePrincipalSecret -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($ServicePrincipalId, $securePassword)

# 查找目标脚本
$scripts = Get-ChildItem -Path $EnvironmentPath -Filter "Create*.sql" -Recurse -File

if ($scripts.Count -eq 0) {
    Write-Host "No Create*.sql scripts found in $EnvironmentPath"
    exit 0
}

Write-Host "Found $($scripts.Count) scripts to run:"
$scripts | ForEach-Object { Write-Host $_.FullName }

# 批量执行脚本，添加错误捕获
foreach ($script in $scripts) {
    try {
        Write-Host "Starting execution of $($script.Name)"
        Invoke-SqlCmd2 -ServerInstance $SynapseServer -Database $DatabaseName -Credential $credential -InputFile $script.FullName
        Write-Host "Successfully executed $($script.Name)"
    }
    catch {
        Write-Error "Failed to execute $($script.Name): $_"
        exit 1
    }
}

然后在Azure DevOps YAML管道中调用该脚本：

- task: PowerShell@2
  displayName: 'Run Synapse Create Scripts'
  inputs:
    targetType: 'filePath'
    filePath: '$(System.DefaultWorkingDirectory)/Run-SynapseCreateScripts.ps1'
    arguments: >
        -EnvironmentPath "syn-ws-${{ parameters.environment }}"
        -SynapseServer "<你的Synapse服务器名>.sql.azuresynapse.net"
        -DatabaseName "<目标数据库名>"
        -ServicePrincipalId $(ServicePrincipalId)
        -ServicePrincipalSecret $(ServicePrincipalSecret)

核心提醒 #

• 确保管道代理能访问Synapse工作区（自托管代理需网络连通，微软托管代理需Synapse允许公共访问或配置VNet集成）
• 执行脚本的身份（服务主体/SQL用户）需拥有Synapse对应权限（如CREATE TABLE、ALTER SCHEMA等，匹配脚本操作）
• 可根据需求添加更精细的错误处理，比如脚本失败时终止管道或生成详细日志

内容的提问来源于stack exchange，提问作者coding