Automate Workday Integration Tasks from PowerShell

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

Getting Started

---

ADO.NET Provider

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

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

To connect to Workday, users need to find the Tenant and BaseURL and then select their API type.

Obtaining the BaseURL and Tenant

To obtain the BaseURL and Tenant properties, log into Workday and search for "View API Clients." On this screen, you'll find the Workday REST API Endpoint, a URL that includes both the BaseURL and Tenant.

The format of the REST API Endpoint is: https://domain.com/subdirectories/mycompany, where:

• https://domain.com/subdirectories/ is the BaseURL.
• mycompany (the portion of the url after the very last slash) is the Tenant.

For example, in the REST API endpoint https://wd3-impl-services1.workday.com/ccx/api/v1/mycompany, the BaseURL is https://wd3-impl-services1.workday.com and the Tenant is mycompany.

Using ConnectionType to Select the API

The value you use for the ConnectionType property determines which Workday API you use. See our Community Article for more information on Workday connectivity options and best practices.

Authentication

Your method of authentication depends on which API you are using.

• WQL, Reports as a Service, REST: Use OAuth authentication.
• SOAP: Use Basic or OAuth authentication.

See the Help documentation for more information on configuring OAuth with Workday.

1. Load the provider's assembly:

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

2. Connect to Workday:

    
   $conn= New-Object System.Data.CData.Workday.WorkdayConnection("User=myuser;Password=mypassword;Tenant=mycompany_gm1;BaseURL=https://wd3-impl-services1.workday.com;ConnectionType=WQL;InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

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

   
   $sql="SELECT Worker_Reference_WID, Legal_Name_Last_Name from Workers"
   
   $da= New-Object System.Data.CData.Workday.WorkdayDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.worker_reference_wid $_.legal_name_last_name
   }
     

Update Workday Data

$cmd =  New-Object System.Data.CData.Workday.WorkdayCommand("UPDATE Workers SET Legal_Name_Last_Name='Morgan' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Workday.WorkdayParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Workday Data

$cmd =  New-Object System.Data.CData.Workday.WorkdayCommand("INSERT INTO Workers (Legal_Name_Last_Name) VALUES (@myLegal_Name_Last_Name)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Workday.WorkdayParameter("@myLegal_Name_Last_Name","Morgan")))
$cmd.ExecuteNonQuery()

Delete Workday Data

$cmd =  New-Object System.Data.CData.Workday.WorkdayCommand("DELETE FROM Workers WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Workday.WorkdayParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()

CodeProject