March 14, 2017 / by Pradeep Vijayakumar / In MuleSoft , Cornerstone onDemand /

Simple Flat File to Web Service

I thought a simple flat file to web service call would be very simple, but it just turned out not to be. Let’s still call it simple because in the end that’s all it is - we get all the data lines within a given CSV file sent over web.

Firstly, the file and it’s quirks

Depending upon various factors including the age of the host, where the host is (both geography and within a given IT environment), the operating system and software etc., the file is encoded. UTF-8 is the recommended encoding for HTML5 (and in turn the most common encoding for any web service calls). But given this is fairly new, most often than not we will find encoding to be different. Flat files as part of data feeds almost always involve a delimiter, most common being tab-delimited text or a comma-separated-value (CSV). In our case, we have a lot of french words (given it’s a France-based system) and the encoding used is ISO-8859-1. It is a TXT file delimited using a ; (semi-colon).

Receiving End

On the other side, it is pretty straight forward SOAP 1.1 web service call to Cornerstone. I’m using SetUsers operation of the ClientDataService of Cornerstone API so I can push basic employee/user data such as name, email address and some employment specific information. Cornerstone does expose WSDL, so it’s pretty standard to use the WSDL in our Web service consumer so any dataweave transformer could let us visually map the fields to start off. Further, Cornerstone currently supports WSSE Username-token security along with PasswordText authentication.

Mule Service Design

Although we could use a Mule flow for this service, a Batch is best suited. A batch automatically processes once per record, helping us catch/throw any exceptions but also proceeding through the file in either cases. We simply have an FTP connector as the input, mainly a dataweave transformer and web service consumer to finish the job off.

Now, It’s Time To Code

<?xml version="1.0" encoding="UTF-8"?>
<mule>
  <ws:consumer-config name="Web_Service_Consumer" service="ClientDataService" port="ClientDataServiceSoap" serviceAddress="https://ws-xyz-pilot.csod.com/feed30/clientdataservice.asmx" wsdlLocation="https://ws-xyz-pilot.csod.com/feed30/clientdataservice.asmx?WSDL" doc:name="Web Service Consumer">
    <ws:security>
      <ws:wss-username-token username="DOM\user" password="password" passwordType="TEXT"/>
    </ws:security>
  </ws:consumer-config>
  <ftp:connector name="FTP" pollingFrequency="120000" validateConnections="true" doc:name="FTP" moveToDirectory="archive"/>
    <batch:job name="fra-csodBatch">
      <batch:input>
        <ftp:inbound-endpoint host="ftphost1.anymule.net" port="21" path="/csod/" user="ftpuser" password="ftpuser" responseTimeout="10000" doc:name="FTP" binary="false"/>
      </batch:input>
      <batch:process-records>
        <batch:step name="Batch_Step">
          <dw:transform-message doc:name="Transform Message">
            <dw:input-payload mimeType="application/csv">
              <dw:reader-property name="separator" value=";"/>
              <dw:reader-property name="header" value="false"/>
            </dw:input-payload>
            <dw:set-payload>
              <![CDATA[
                %dw 1.0
                %output application/xml
                %namespace ns1 urn:Cornerstone:ClientDataService
                %namespace ns0 urn:Cornerstone:ClientData
                ---
                {
	                ns1#SetUsers: {
		                ns0#Users: {
			                (payload map ((payload01 , indexOfPayload01) -> {
				                ns0#User @(Active: true , Id: payload01[0] , AllowReconcile: true , Absent: false): {
                          ns0#Contact: {
						                ns0#Name @(Prefix: payload01[1] , First: payload01[5] , Last: payload01[4]): null,
						                ns0#Email: payload01[14] ++ payload01[143],
						                ns0#Phone: payload01[13] replace "." with "" as :string
					                },
					                ns0#Organization @(Approvals: 1): {
						                ns0#Unit @(Type: "Division"): "ABC-" ++ payload01[100],
						                ns0#Unit @(Type: "Position"): "POS1" when payload01[58] == "P1" otherwise "PALL",
						                ns0#Unit @(Type: "Location"): "F-" ++ payload01[100],
						                ns0#Employment @(LastHireDate: payload01[98] as :date { format: "dd/MM/yyyy" }, OriginalHireDate: payload01[98] as :date { format: "dd/MM/yyyy" }): null,
						                ns0#Manager: payload01[142]
					                },
					                ns0#Demographic: {
						              ns0#Gender: "Male" when payload01[1] == "M." otherwise "Female"
					              },
					              ns0#Custom: {
						              ns0#Field @(Name: "F1"): "test1",
                    	    ns0#Field @(Name: "F2"): "test2"
					              }
				              }
			              }))
		              }
	              }
              }
            ]]>
          </dw:set-payload>
        </dw:transform-message>
        <ws:consumer config-ref="Web_Service_Consumer" operation="SetUsers" doc:name="Web Service Consumer"/>
      </batch:step>
    </batch:process-records>
  </batch:job>
</mule>

As you can see, most settings are standard for a given mule component. Let’s go through few key ones -

1. We choose to move the file to an archive folder once the transfer has gone through successfully.

moveToDirectory=”archive”

1. Dataweave code is defined by the data and the structure at hand. Notable pieces include the concatenation used on two fields for Email, gender information being derived using name prefix and the replacement of . (period) character usually used in France for phone numbers.

ns0#Email: payload01[14] ++ payload01[143], ns0#Phone: payload01[13] replace “.” with “” as :string

ns0#Gender: “Male” when payload01[1] == “M.” otherwise “Female”

Build And Deploy - Successful?

Nope. The error we get is -

Object “[B” not of correct type. It must be of type “{interface java.lang.Iterable,interface java.util.Iterator,interface org.mule.routing.MessageSequence,interface java.util.Collection}” (java.lang.IllegalArgumentException)

The process phase of a batch expects a collection of records, which it can iterate through and not the whole payload that consists of all the lines of the flat file. This a bit tricky because I couldn’t find the FTP connector having any options that we can use in case of Batch to automatically output a collection. I think MuleSoft needs to provide an option here so it could do it in a seamless manner. To overcome, we add a splitter and a collection-aggregator -