Search code examples
azure-devopsazure-pipelinesazure-synapse-analytics

Azure DevOps yaml file to run pipeline in Azure Synapse Analytics

I am working on Azure DevOps and Azure Synapse Analytics. Right now, I have the issue to integration Azure DevOps with Azure Synpase Analytics together. How could I use the yaml file and azure powershell in azure DevOps together to activate the pipeline in azure synapse analytics or how to launch the pipeline in azure synapse via Azure DevOps yaml file?

update: could you provide me how to use the service connection and how to implement a powershell script in azure DevOps instead of using a incline script in the YAML pipeline definition.

Solution

  • Update

    Based on the discussions and updates, let's assume you have got a service principal (app registration) and granted it with the Synapse Administrator role in your Azure Synapse workspace like below.

    Image

    We can proceed to manually create a new Azure Resource Manager service connection in Azure DevOps for the AzurePowerShell pipeline task to authenticate against this service principal with Workload identity federation authentication, which can avoid client secret leakage or issue caused by its expiration. Here are the brief steps for your reference.

    1. In Azure Portal. navigate to Microsoft Entra Id (formerly Azure Active Directory) -> App registrations -> Select the app for authentication -> Keep note of the Application (client) ID and Directory (tenant) ID;

      enter image description here

    2. In Azure Portal, collect your Subscription ID and name; Go to the resource group where your Azure Synapse workspace resource locates -> As the resource group Owner, you should be able to assign the app with the Contributor role to the scope of this resource group (this RBAC role assignment is for ARM service connection validation; the permission may not be minimum but is the same role assignment during an automated ARM service connection creation);

      enter image description here

      enter image description here

    3. In Azure DevOps, browse to your Project Settings -> Pipelines -> Service connections -> New service connection -> Select the type of Azure Resource Manager -> Select Workload Identity federation (manual) -> Give a name for the ARM service connection;

      enter image description here

    4. Keep note of the Issuer and Subject identifier -> Input the Subscription Id, Subscription Name, Service Principal Id and Tenant ID from step 1 & 2; you can save the new ARM service connection as a draft now and move on to next step; enter image description here

    5. In Azure Portal again, Select the app -> Click on Certificates & secrets blade -> Click on Federated credentials tab -> Add credential -> Select Other issuer and Input the Issuer and Subject identifier from step 4;

      enter image description here

    6. Go back to Azure DevOps ARM service connection draft to Finish setup -> Click on the Verify and save button;

      enter image description here

    Per the requirement not to use the inline script in YAML pipeline definition, you may add a .ps1 script file with the sample command below in your repo first. In your Azure DevOps YAML pipeline definition file, you can reference the new ARM service connection by its name in the AzurePowerShell pipeline task and run the script file with FilePath.

    Invoke-AzSynapsePipeline.ps1

    Invoke-AzSynapsePipeline -WorkspaceName "$env:MY_WORKSPACE" -PipelineName "$env:PIPELINE_NAME"

    Image

    azure-pipeline.yml

    variables:
  myWorkspace: azsynapsexxxxxx0
  pipelineName: Pipeline 1

pool:
  vmImage: windows-latest

steps:
- task: AzurePowerShell@5
  inputs:
    azureSubscription: 'ARMSvcCnnAzureSynapseResource0'
    ScriptType: 'FilePath'
    ScriptPath: 'Invoke-AzSynapsePipeline.ps1'
    azurePowerShellVersion: 'LatestVersion'
  env:
    MY_WORKSPACE: $(myWorkspace)
    PIPELINE_NAME: $(pipelineName)

    Image

    According to this document on Pipeline execution and triggers - Azure Data Factory & Azure Synapse, you may test to run the Invoke-AzSynapsePipeline Azure PowerShell command in the AzurePowerShell@5 DevOps pipeline task to trigger your Azure Synapse pipeline.

    Here is my sample YAML Azure DevOps pipeline for your reference.

    pool:
  vmImage: windows-latest

steps:
- task: AzurePowerShell@5
  inputs:
    azureSubscription: 'ARMSvcCnnWIFAutoSub1'
    ScriptType: 'InlineScript'
    Inline: |
      Invoke-AzSynapsePipeline -WorkspaceName $(myWorkspace) -PipelineName "Pipeline 1"
    azurePowerShellVersion: 'LatestVersion'

    Image If you already created an Azure Resource Manager service connection in Azure DevOps, please make sure its underly service principle that the Azure DevOps pipeline authenticates against is granted with sufficient permission to trigger Azure Synapse pipeline.

    Image