Automate Workday Integration Tasks from PowerShell

The CData Cmdlets for Workday are standard PowerShell cmdlets 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

---

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to Workday, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete Workday data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for Workday. To access Workday data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Workday.

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.

PowerShell

1. Install the module:

   Install-Module WorkdayCmdlets

2. Connect:

   $workday = Connect-Workday -User "$User" -Password "$Password" -Tenant "$Tenant" -BaseURL "$BaseURL" -ConnectionType "$ConnectionType" -InitiateOAuth "$InitiateOAuth"
   

3. Search for and retrieve data:

   $legal_name_last_name = "Morgan"
   $workers = Select-Workday -Connection $workday -Table "Workers" -Where "Legal_Name_Last_Name = `'$Legal_Name_Last_Name`'"
   $workers
   

   You can also use the Invoke-Workday cmdlet to execute SQL commands:

   $workers = Invoke-Workday -Connection $workday -Query 'SELECT * FROM Workers WHERE Legal_Name_Last_Name = @Legal_Name_Last_Name' -Params @{'@Legal_Name_Last_Name'='Morgan'}
   

ADO.NET

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

PowerShell

Update-Workday -Connection $Workday -Columns @('Worker_Reference_WID','Legal_Name_Last_Name') -Values @('MyWorker_Reference_WID', 'MyLegal_Name_Last_Name') -Table Workers -Id "MyId"

ADO.NET

$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

PowerShell

Add-Workday -Connection $Workday -Table Workers -Columns @("Worker_Reference_WID", "Legal_Name_Last_Name") -Values @("MyWorker_Reference_WID", "MyLegal_Name_Last_Name") 

ADO.NET

$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

PowerShell

Remove-Workday -Connection $Workday -Table "Workers" -Id "MyId"

ADO.NET

$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