Load SQL Server data to Workday using SSIS / SOAP API

Step-By-Step : Import SQL Server data to Workday using SSIS

Lets build our SSIS Package to load data from SQL Server or any Source (e.g. Oracle, DB2) to Workday using SOAP API calls. Using below approach you can Create new records or Update existing records in Workday. You can also delete records with same concept.

Basic steps outlined below.

1. Fetch records from SQL Source and build XML Request for each Row (e.g. Create Account)
2. Build XML Request (SOAP Body) using Template Transform or  SSIS XML Generator Transform. If you have Array nodes (e.g. One to Many) then you have to use SSIS XML Generator Transform else use Template Transform for ease of use.
3. Pass input record (e.g. SOAP Body) to Web API destination to call Workday API call (CREATE, UPDATE, DELETE requests)
4. Parse XML Response (i.e. output) using SSIS XML Parser Transform or save raw XML to SQL Server database.
5. Redirect Bad rows or failed requests to log file for review from Workday Course

Obtain Workday API URL

Once you have WSDL file, next step is craft correct URL for API service you like to call.

Syntax: https://<workday host name>.workday.com/ccx/service/<tenant name>/<service-name>
Example: https://MY-INSTANCE.workday.com/ccx/service/MY-TenantID/Human_Resources

Craft SOAP Body (XML API Request) using SoapUI

Now its time to craft some SOAP Request. Check steps outlined here (Use SoapUI tool) . Once you have Request Body XML you can change parameters as per your need.

Creating SSIS Connection for Workday SOAP API call using WSS Security

To create a new connection for workday perform the following steps.

1. Two ways you can create HTTP connection for the workday service
   First approach: Right click in the connection managers panel and click “New Connection…” and Select ZS-HTTPconnection from the connection type list and click OK.
   — OR —
   Second approach: If you are already on SSIS XML Source or SSIS REST API TASK or SSIS Web API Destination UI then click [New] next to the Connection Dropdown.
2. Once HTTP Connection UI is visible configure following way.
   1. Enter API URL for Workday (Make sure you don’t enter WSDL URL  ). Your API URL will be something like below.
      Syntax:  https://<workday host name>.workday.com/ccx/service/<tenant name>/<service-name>
      Example:  https://wd1-impl-services1.workday.com/ccx/service/MyTenantID/Human_Resources
   1. Select credential type as SOAP WSS (This setting is only found in v2.6.4 or Higher)
   1. Enter your workday userid and password
   1. For WSS password type setting you leave it default (Not set) or change to PasswordHash for more secure communication.
   1. Click OK to save. Get more skills from Workday Online Training

Loading SQL Server data to Workday using SSIS

Let’s look at the real-world scenario. You have Accounts table stored in SQL Server and you like to create same accounts in Workday by calling appropriate API calls.

1. Drag Data flow task from SSIS Toolbox. Double click to edit.
2. Drag OLEDB Source configure to read SQL Table (e.g. Accounts)
3. Drag ZS Template Transform from the toolbox. Connect OLEDB Source to Template Transform. If you need flexible XML Generation then use XML Generator Transform but it may require some learning curve so for simplicity we are skipping that from this article.
4. Enter your SOAP request in the template text (like below) you like to call (This is obtained from the previous section – using tool like SoapUI)
   For Example: To create a new account you can use enter like below. Replace xxxxxxxxxx with Columns placeholder.
   To insert Column name as Placeholder click <<Insert Placeholder>> and then Select [Columns] node. Template Transform outputs column name TemplateOutput. You can use this as Body to feed next step (i.e. Call Workday API using Web API Destination )When you insert placeholder to make sure you use XML Encoded Columns if you expecting a Long text or special characters part of your data. Learn more from Workday Certification
   Syntax for encoded value is    <%CustomerName,FUN_XMLENC%>  . You don’t need FUN_XMLENC for numeric fields. For normal placeholder without encoding use just name with column placeholder indicators e.g.   <%Amount%>

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:bsvc="urn:com.workday/bsvc">
   <soapenv:Header/>
   <soapenv:Body>
      <bsvc:Workday_Account_for_Worker_Add>
         <bsvc:Worker_Reference>
            <!--You have a CHOICE of the next 2 items at this level-->
            <bsvc:Employee_Reference>
               <bsvc:Integration_ID_Reference>
                  <bsvc:ID>xxxxxxxxxx</bsvc:ID>
               </bsvc:Integration_ID_Reference>
            </bsvc:Employee_Reference>
            <bsvc:Contingent_Worker_Reference>
               <bsvc:Integration_ID_Reference>
                  <bsvc:ID>xxxxxxxxxxxx</bsvc:ID>
               </bsvc:Integration_ID_Reference>
            </bsvc:Contingent_Worker_Reference>
         </bsvc:Worker_Reference>
         <bsvc:Workday_Account_for_Worker_Data>
            <!--type: string-->
            <bsvc:User_Name>xxxxxxxxxxxxxx</bsvc:User_Name>
         </bsvc:Workday_Account_for_Worker_Data>
      </bsvc:Workday_Account_for_Worker_Add>
</soapenv:Body>
</soapenv:Envelope>

To get in-depth knowledge, enroll for a live free demo on Workday Training