Automate Zendesk Integration Tasks from PowerShell

The CData ADO.NET Provider for Zendesk 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 Zendesk.

ADO.NET Provider

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

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

Connecting to Zendesk

To connect, set the URL and provide authentication. The URL is your Zendesk Support URL: https://{subdomain}.zendesk.com.

Authenticating to Zendesk

You can authenticate using the Basic or OAuth methods.

Using Basic Authentication

To use Basic authentication, specify your email address and password or your email address and an API token. Set User to your email address and follow the steps below to provide the Password or ApiToken.

• Enable password access in the Zendesk Support admin interface at Admin > Channels > API.
• Manage API tokens in the Zendesk Support Admin interface at Admin > Channels > API. More than one token can be active at the same time. Deleting a token deactivates it permanently.

Using OAuth Authentication

See the Getting Started guide in the CData driver documentation for an authentication guide.

1. Load the provider's assembly:

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

2. Connect to Zendesk:

    
   $conn= New-Object System.Data.CData.Zendesk.ZendeskConnection("URL=https://subdomain.zendesk.com;[email protected];Password=test123;InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

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

   
   $sql="SELECT Id, Subject from Tickets"
   
   $da= New-Object System.Data.CData.Zendesk.ZendeskDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.id $_.subject
   }
     

Update Zendesk Data

$cmd =  New-Object System.Data.CData.Zendesk.ZendeskCommand("UPDATE Tickets SET Industry='Floppy Disks' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Zendesk.ZendeskParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Zendesk Data

$cmd =  New-Object System.Data.CData.Zendesk.ZendeskCommand("INSERT INTO Tickets (Industry) VALUES (@myIndustry)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Zendesk.ZendeskParameter("@myIndustry","Floppy Disks")))
$cmd.ExecuteNonQuery()

Delete Zendesk Data

$cmd =  New-Object System.Data.CData.Zendesk.ZendeskCommand("DELETE FROM Tickets WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Zendesk.ZendeskParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()