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 access to Workday.

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 retrieve 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, there are three pieces of information required: Authentication, API URL, and WSDL URL.

Authentication

To authenticate, specify your User and Password. Note that you must append your Tenant to your User separated by an '@' character. For instance, if you normally log in with 'geraldg' and your Tenant is 'mycompany_mc1', then your User should be specified as 'geraldg@mycompany_mc1'.

API URL

The API URL may be specified either directly via APIURL, or it may be constructed from the Tenant, Service, and Host. The APIURL is constructed in the following format: <Host>/ccx/service/<Tenant>/<Service>.

WSDL URL

The WSDLURL may be specified in its entirety, or may be constructed from the Service and WSDLVersion connection properties. The WSDLURL is constructed in the following format: https://community.workday.com/sites/default/files/file-hosting/productionapi/<Service>/<WSDLVersion>/<Service>.wsdl

PowerShell

1. Install the module:

   Install-Module WorkdayCmdlets

2. Connect:

   $workday = Connect-Workday -User "$User" -Password "$Password" -Tenant "$Tenant" -Host "$Host"

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;Host=https://wd3-impl-services1.workday.com") $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 }