Web service sources

Data that comes from a web service typically has a hierarchical structure. For example, when you use a Workday v2 source connection, the data passes as XML with a hierarchical structure.

When you select a web service connection for a Source transformation, you perform the following steps to configure the transformation:

For example, you want to include worker information from Workday in a mapping with a relational database target. You create a Source transformation and select the Workday connection. You select the Get_Workers operation, which pulls the worker data in a defined XML structure. You define an advanced filter so that only name and contact information enters the data flow. You define a relational structure for the worker data and then map the fields to fields in the target database.

When you define the source properties for a web service connection, you select the web service operation. The available operations are determined by the connection. For example, for a Workday connection, Get_Workers is an operation.

The request message is in XML format. To customize the request message, you can begin with a template that includes the necessary formatting for the message. The request message template shows the contents for the selected operation.

Copy and paste the template into the Request Message editor pane and then revise the message.

For example, if you want to include transactions occurring between January 1, 2016 and March 31, 2016, you can enter the Effective_From and Effective_Through values in the request message, as shown in the following image:

You can parameterize the request message using in-out parameters. For example, instead of using specific Effective_From and Effective_Through dates in the message, you can use $$Effective_From and $$Effective_Through parameters. You need to create the in-out parameters in the Parameters panel before you can use them in the request message.

For more information about in-out parameters, see the "Parameters" section in Mappings.

Be sure you use well-formed XML formatting in the request message. You can validate the message to be sure that the XML matches the structure expected by operation.

The response shown in the Field Mapping tab shows the hierarchical structure of the data that comes from the source.

When you select fields in the Response area, the fields appear in the Output Fields area in a relational structure with generated primary keys and foreign keys. For example, in the Response Fields area you select First_Name and Last_Name, and then you select Email_Address, which is located under a different parent in the hierarchy. In the Output Fields area, the structure is relational with assigned primary and foreign keys, as shown in the following image:

Consider cardinality when you map response fields to the relational structure. Cardinality imposes constraints on the number of times a field or group can occur at a specific point in the XML structure. A cardinality of 0-many means the field or group can have zero to many occurrences. A cardinality of 1-1 means a field or group is required and can only occur once.

If you map a field with 0-1 or 1-1 cardinality, the first parent node that has 0 to more than 1 cardinality is also mapped. If a parent group with 0 to more than 1 cardinality does not exist, the system creates a group. For example, if you map Email_Comment, which has cardinality of 0-1, the Email_Address_Data group, which has cardinality of 0-many, is automatically mapped.