My Scenario:
As as System Admin/DevOps engineer, maintaining the variable group is little tricky as its very difficult to maintain the history and changes. We got the requirement in one of our migration project with more number of variables for the pipeline with values in excel. It’s definitely very easy to copy/paste as there are just 20 Key-Value pairs in this scenario. However, think about a scenario where you need to repeat this for many Variable Groups for multiple project? It’s definitely a tedious job and Manually creating this new key values in the variable group will make more time and surely there will be human error. So to overcome this problem, we have though to Import bulk Variables to Variable Group using Azure DevOps CLI
What format we got the excel?
Instead of adding them directly from the Azure DevOps Portal, we will leverage automation the Process of automatically adding the Key-Value pairs without doing any manual Data-Entry job as we got huge number or variables.
Note: It’s definitely very easy to copy/paste as there are just 10/20 Key-Value pairs in this scenario. However, think about a scenario where you need to repeat this for many Variable Groups for multiple project? It’s definitely a very tedious job and surely there will be an human-error.
Prerequisite
Step 1: Retrieve the Variable Group ID:
The Variable group need to be ready for importing the variable from the excel. For this example, i already created one variable group know as “mytestvariablegroup” (as shown in below snap) and noted the variable group id (this id will be unique for each variable group) as shown below. In my case, the Variable Group ID is 1 as shown in the below snap shot. This ID will be used in the Step4 and Step5 to dynamically create the Variables using the Azure DevOps CLI commands.
Step 2: Generate Azure DevOps CLI commands using Excel Formula
Navigate to the excel sheet add another column and paste the below formula. The C2 and D2 will be column which containing the variable name and variable value. And, apply the formula to all the rows. Once you apply the formula to all the rows, it should look something like below.
|
1 2 3 |
=CONCAT(“az pipelines variable-group variable create –group-id 2 –name “””,B2,””” –value “””,C2,””””) |
Import bulk Variables to Variable Group using Azure DevOps CLI
Step 3: Login to Azure DevOps from Command Line
Non-interactive mode
Before we start with Azure CLI commands, it’s mandatory to authenticate using Azure DevOps credentials. If you are using same account for both Azure and Azure DevOps then you can use the below command to authenticate.
|
1 2 3 |
Az login |
Step 4: Set Default Organization
Run the below command to set the organization where we are going to update the variable.
|
1 2 3 |
az devops configure -d organization=https://dev.azure.com/thiyaguDevops/ |
Step 5: Set Default Project
Run the below command to set the default Project.
|
1 2 3 |
az devops configure -d project=poc |
Step 6: Execute the Azure DevOps CLI commands
In the step 2, we generated all the commands in excel. Now, it’s time to execute them. Copy the entire rows which containing the formula (column D , without header if any only the values) of commands and paste all of them at once in the command prompt.
Note: No need to copy past one by one from excel, copy all from the Colum D and past at single time, remaining the PowerShell will take care
Step7: Review the Output.
Finally now it’s time to view the results in our variable group. Navigate to the Variable Group and refresh the page to view all the new variables are added like as shown below
