Managing Synapse Triggers with PowerShell in DevOps

Hope you all are doing good, Today’s blog we will be diving into the PowerShell Script that we will use to toggle any Triggers ON and OFF within the synapse workspace using the DevOps Pipeline.

This script is tailored to toggle the specific triggers OFF and ON from the DevOps pipeline pre and post deployment respectively.

Toggling trigger is a common process in the CI/CD solution in the project. Before the deployment, all the triggers needs to be stopped else the deployment fails because it cannot override the artifacts on the target.

You may ask, why we are not using the inbuilt task to toggle triggers? Well, yes you could use that if you have less triggers in your environment but if you have more then does not work for more than a certain number of triggers, which is bug we can say that might get rectified in the upcoming release of the task. In this scenario, the PowerShell Script comes handy to tackle.

Let’s understand each step for the PowerShell Script.

You need to define the variable RESOURCE_GROUP, WORKSPACE_NAME, TRIGGER

#defining the parameters
$resourcegroup = '$(RESOURCE_GROUP)'
$workspacename = '$(WORKSPACE_NAME)'

#provide the trigger names by comma separated
# Note: Do not keep the space while providing the values. example ‘trigger1,trigger2)

$trigger_list = '$(TRIGGER)'

We will extract the triggers from the input trigger into the variable which we will use later on the script.

$triggers = $trigger_list.Split(',')

It’s a good practise to verify the workspace which is the input to find the workspace exists in the Resource Group or not. We will use Get-AzSynpaseWorkspace cmdlet, if the input workspace does not exist in the given resource group then we are writing output on console and the code breaks and come out. However, if the workspace exists then it will output the workspace as object.

Write-Output "verifying the workspace.."
$workspace = Get-AzSynapseWorkspace -ResourceGroupName $resourcegroup -Name $workspacename
if(-not($workspace)) {Write-Output ("The workspace {0} not found in the resourcegroup {1}" -f $workspacename, $resourcegroup)}
write-output $workspace 

Post verifying the workspace, we will validate the existence of the triggers provided in the input in the provided Synapse Workspace. We have taken the variable $triggers which holds all the triggers after splitting them.

Here we are looping through each of the trigger and passing through the cmdlet Get-AzSyanpseTrigger by passing the workspace name and the trigger as input to the cmdlet.

If there isn’t any triggers present in the workspace then it exit the code.

#verifying each of the triggers
Write-Output "Verifying the triggers in the workspace.."
$trigger = $triggers | ForEach-Object {Get-AzSynapseTrigger -WorkspaceObject $workspacename -Name $_}
Write-Output ("found {0} triggers in the workspace" -f -$trigger.Count)
if(-not($trigger)) { exit }

Up to this point, we have successfully identified the Synapse Workspace and the triggers available within it. Now, it’s time to examine the current state of these triggers—whether they are in the ON or OFF state. Once we confirm the presence of triggers in the workspace, our next step is to assess the status of each trigger in the provided list. Specifically, we aim to identify which triggers are currently active and need to be deactivated. To achieve this, we employ a loop to iterate through each trigger found in the workspace, retrieving their properties. Simultaneously, within the same step, we utilize another foreach loop to pass each trigger through the Stop-AzSynapseTrigger cmdlet, effectively turning them OFF.

if($triggers.Count -gt 0)
          {
          Write-Output "Getting the started triggers that needs to be stopped"
          $startedtriggers = $trigger | Where-Object {$_.Properties.RuntimeState -eq "Started"}
          Write-Output ("Found {0} active triggers in the workspace" -f $startedtriggers.Count)
        foreach($t in $startedtriggers){
        try{
        Write-Output ("Stopping " -f $t.name)
        $result = Stop-AzSynapseTrigger -WorkspaceName $workspacename -Name $t.Name
        }
        catch {
                write-output ("Something is wrong with the trigger {0}" -f $t.Name)
                Write-Output ($_.Exception | Format-List -Force)
                }
        Write-Output "Done..."
        }
        }

The above code is to Stop the trigger if the trigger is active which is the first step before deploying any artifacts from DevOps, The same code can be tailored to turn the Triggers ON post deployment. You have to copy the same script with some changes. So Instead of Stopping the Trigger you have to Start it, for that you need to replace the cmdlet Stop-AzSynapseTrigger to Start-AzSynapseTrigger and Change the Propeties.RuntimeState to ‘Started’. This will ensure that the code is now modified to Start the stopped triggers.

Now the final step is to paste the code in the PowerShell Command Task in the DevOps Pipeline. You can use the classic editor to create a new pipeline. Make sure you have variables you need to pass in the pipeline variable.

You need to use the PowerShell Task to run this PowerShell Script. Fill in the details for the task and paste you script. Once you are done configuring the task then save and create the release to run the release pipeline.

Before run:

After execution, it successfully Turned the Triggers OFF.

Hope this blog helped you to get some insights on the PowerShell Script to Toggle any Synapse trigger.

Note: As of now the cmdlet Stop-AzSynapseTrigger and Start-AzSynapseTrigger has some bug while using from the PowerShell command. Hope the Microsoft PG team will rectify this in the upcoming releases.