Automate Jira Assets Integration Tasks from PowerShell

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

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for Jira Assets; this tutorial shows how to use the Provider to create, retrieve, update, and delete Jira Assets data.

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

Jira Assets supports connecting and authenticating via the APIToken.

To generate an API token:

1. Log in to your Atlassian account.
2. Navigate to Security < Create and manage API Token < Create API Token.

Atlassian generates and then displays the API token.

After you have generated the API token, set these parameters:

• AuthScheme: APIToken.
• User: The login of the authenticating user.
• APIToken: The API token you just generated.

You are now ready to connect and authenticate to Jira Assets.

1. Load the provider's assembly:

   
   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Jira Assets\lib\System.Data.CData.JiraAssets.dll")
       

2. Connect to Jira Assets:

    
   $conn= New-Object System.Data.CData.JiraAssets.JiraAssetsConnection("User=MyUser;APIToken=myApiToken;Url=https://yoursitename.atlassian.net")
   $conn.Open()
   

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

   
   $sql="SELECT ID, Name from Objects"
   
   $da= New-Object System.Data.CData.JiraAssets.JiraAssetsDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.id $_.name
   }
     

Update Jira Assets Data

$cmd =  New-Object System.Data.CData.JiraAssets.JiraAssetsCommand("UPDATE Objects SET Label='SYD-1' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.JiraAssets.JiraAssetsParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Jira Assets Data

$cmd =  New-Object System.Data.CData.JiraAssets.JiraAssetsCommand("INSERT INTO Objects (Label) VALUES (@myLabel)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.JiraAssets.JiraAssetsParameter("@myLabel","SYD-1")))
$cmd.ExecuteNonQuery()

Delete Jira Assets Data

$cmd =  New-Object System.Data.CData.JiraAssets.JiraAssetsCommand("DELETE FROM Objects WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.JiraAssets.JiraAssetsParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()