Automate Azure DevOps Integration Tasks from PowerShell

The CData ADO.NET Provider for Azure DevOps is a standard ADO.NET Provider that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time access to Azure DevOps.

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for Azure DevOps; this tutorial shows how to use the Provider to retrieve Azure DevOps data.

Once you have acquired the necessary connection properties, accessing Azure DevOps data in PowerShell can be enabled in three steps.

You can connect to your Azure DevOps account by providing the Organization and PersonalAccessToken.

Obtaining a Personal Access Token

To generate one, log in to your Azure DevOps Organization account and navigate to Profile -> Personal Access Tokens -> New Token. The generated token will be displayed.

If you wish to authenticate to Azure DevOps using OAuth refer to the online Help documentation for an authentication guide.

1. Load the provider's assembly:

   
   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Azure DevOps\lib\System.Data.CData.AzureDevOps.dll")
       

2. Connect to Azure DevOps:

    
   $conn= New-Object System.Data.CData.AzureDevOps.AzureDevOpsConnection("AuthScheme=Basic;Organization=MyAzureDevOpsOrganization;ProjectId=MyProjectId;PersonalAccessToken=MyPAT;InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

3. Instantiate the AzureDevOpsDataAdapter, execute an SQL query, and output the results:

   
   $sql="SELECT Id, BuildNumber from Builds"
   
   $da= New-Object System.Data.CData.AzureDevOps.AzureDevOpsDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.id $_.buildnumber
   }