How to generate Workday SOAP Request XML in SSIS – ZappySys Help Center

In this article, you will learn how to create XML for Workday SOAP API call in SSIS.

If you are new to SSIS and Workday API then read following 3 articles.

And here are few more

Now let's look at one specific Example Workday Finacial API (Import Statistics)

WSDL is found below. You can import into SoapUI and try yourself

https://community.workday.com/custom/developer/API/Financial_Management/v26.1/Financial_Management.wsdl

Assume that we have to achieve the following Request XML for SOAP Request.

<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:bsvc="urn:com.workday/bsvc" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
  <soapenv:Header>
    <bsvc:Workday_Common_Header />
  </soapenv:Header>
  <soapenv:Body>
    <bsvc:Import_Statistic_Request bsvc:Version="1.0">
      <bsvc:Add_Only>false</bsvc:Add_Only>
      <bsvc:Statistic_Data>
        <Statistic_ID>xxx</Statistic_ID>
        
        <bsvc:Statistic_Values_Replacement_Data>
          <Statistic_Definition_ID>xxx</Statistic_Definition_ID>
          <Statistic_Line_Value>0</Statistic_Line_Value>
        </bsvc:Statistic_Values_Replacement_Data>
        <bsvc:Statistic_Values_Replacement_Data>
          <Statistic_Definition_ID>xxx</Statistic_Definition_ID>
          <Statistic_Line_Value>0</Statistic_Line_Value>
        </bsvc:Statistic_Values_Replacement_Data>
        <bsvc:Statistic_Values_Replacement_Data>
          <Statistic_Definition_ID>xxx</Statistic_Definition_ID>
          <Statistic_Line_Value>6</Statistic_Line_Value>
        </bsvc:Statistic_Values_Replacement_Data>
        <bsvc:Statistic_Values_Replacement_Data>
          <Statistic_Definition_ID>xxx</Statistic_Definition_ID>
          <Statistic_Line_Value>3</Statistic_Line_Value>
        </bsvc:Statistic_Values_Replacement_Data>
        <bsvc:Statistic_Values_Replacement_Data>
          <Statistic_Definition_ID>xxx</Statistic_Definition_ID>
          <Statistic_Line_Value>0</Statistic_Line_Value>
        </bsvc:Statistic_Values_Replacement_Data>
        <bsvc:Statistic_Values_Replacement_Data>
          <Statistic_Definition_ID>xxx</Statistic_Definition_ID>
          <Statistic_Line_Value>8</Statistic_Line_Value>
        </bsvc:Statistic_Values_Replacement_Data>

      </bsvc:Statistic_Data>
    </bsvc:Import_Statistic_Request>
  </soapenv:Body>
</soapenv:Envelope>

Here is how we can do it in SSIS

SQL script to create a Test table and sample data

Run following script to build test data.


use Test
go
CREATE TABLE [dbo].[DailyPerformance](
 [Statistic_ID] [nvarchar](255) NULL,
 [Statistic_Definition_ID] [nvarchar](255) NULL,
 [Ledger_Type_ID] [nvarchar](255) NULL,
 [Fiscal_Year_ID] [nvarchar](255) NULL,
 [Fiscal_Schedule_ID] [nvarchar](255) NULL,
 [Fiscal_Posting_Interval_ID] [nvarchar](255) NULL,
 [Statistic_Line_Value] [float] NULL,
 [Customer_Reference_ID] [nvarchar](255) NULL,
 [Custom_Organization_Reference_ID] [nvarchar](255) NULL
) ON [PRIMARY]
GO
USE [Test]
GO
INSERT INTO [dbo].[DailyPerformance]
(Statistic_Definition_ID,Statistic_ID,Ledger_Type_ID,Fiscal_Year_ID,Fiscal_Schedule_ID,Fiscal_Posting_Interval_ID
,Statistic_Line_Value,Customer_Reference_ID,Custom_Organization_Reference_ID
)
 select 'DEF001','SD_Sch_Depart_Actual_2016_Feb_28','Actual','2016','YE','Feb','11234','001-AA','AA175'
union all select 'DEF001','SD_Sch_Depart_Actual_2016_Feb_28','Actual','2016','YE','Feb','2434','006-DL','DL175'
union all select 'DEF001','SD_Sch_Depart_Actual_2016_Feb_28','Actual','2016','YE','Feb','564','012-UA','UA170'
union all select 'DEF002','SD_Sch_Depart_Actual_2016_Feb_28','Actual','2016','YE','Feb','890','012-UA','UA175'
union all select 'DEF002','SD_Actual_Depart_Actual_2016_Feb_28','Actual','2016','YE','Feb','11245','001-AA','AA175'
union all select 'DEF002','SD_Actual_Depart_Actual_2016_Feb_28','Actual','2016','YE','Feb','23445','006-DL','DL175'
union all select 'DEF002','SD_Actual_Depart_Actual_2016_Feb_28','Actual','2016','YE','Feb','2345','012-UA','UA170'
union all select 'DEF002','SD_Actual_Depart_Actual_2016_Feb_28','Actual','2016','YE','Feb','4545','012-UA','UA175'
union all select 'DEF003','SD_Cancel_Flights_Actual_2016_Feb_28','Actual','2016','YE','Feb','112','001-AA','AA175'
union all select 'DEF003','SD_Cancel_Flights_Actual_2016_Feb_28','Actual','2016','YE','Feb','90','006-DL','DL175'
union all select 'DEF003','SD_Cancel_Flights_Actual_2016_Feb_28','Actual','2016','YE','Feb','67','012-UA','UA170'
union all select 'DEF003','SD_Cancel_Flights_Actual_2016_Feb_28','Actual','2016','YE','Feb','345','012-UA','UA175'

Building XML for Workday SOAP Request - Layout Steps

Now let's look at a few steps to build desired XML Shape

Try to build the following ways to achieve desired SOAP XML. For simplicity not all steps listed but you will get an idea of how to achieve it.

1. Configure 2 OLEDB Sources using the below queries provided below

Root Source Query

Select distinct Statistic_Definition_ID, Statistic_ID, Ledger_Type_ID, Fiscal_Year_ID, Fiscal_Schedule_ID, Fiscal_Posting_Interval_ID 
from DailyPerformance

Child Source Query

select Statistic_Definition_ID, Statistic_Line_Value, Customer_Reference_ID, Custom_Organization_Reference_ID from DailyPerformance

2. Setup SSIS XML Generator Transform

Drag SSIS XML Generator Transform and setup as below.

3. Setup XML namespaces and other options

4. Remove Row element name from the Root dataset

5. Add Header Node (add as unbound element)

6. Add Body Node (add as an unbound element)

7. Add Workday_Common_Header under Header (add as unbound element)

After add, it will be like below

8. Add Request node under the body (as unbound)

9. Add version attribute

10. Add Add_Only static element

11. Add Statistic_Data Section (nested unbound)

12. Add Statistic_ID Element

13. Add Array Statistic_Values_Replacement_Data

14. Add few elements under the Array node