Automate Teamgate Integration Tasks from PowerShell

The CData API Driver for ADO.NET 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 Teamgate.

ADO.NET Provider

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

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

Start by setting the Profile connection property to the location of the Teamgate Profile on disk (e.g. C:\profiles\Teamgate.apip). Next, set the ProfileSettings connection property to the connection string for Teamgate (see below).

Teamgate API Profile Settings

To obtain your API Key, log into Teamgate and navigate to Settings > Additional Features > External Apps > New API Key Request. For your Auth Token, go to My Profile > Integrations > API Access > Auth Token.

1. Load the provider's assembly:

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

2. Connect to Teamgate:

    
   $conn= New-Object System.Data.CData.API.APIConnection("Profile=C:\profiles\Teamgate.apip;ProfileSettings='APIKey=your_api_key;AuthToken=your_auth_token';")
   $conn.Open()
   

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

   $sql="SELECT Id, Status from Activities"
   
   $da= New-Object System.Data.CData.API.APIDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.id $_.status
   }