Marketo Connection using SAP PI

[vijayanandpaul_puvvula3]

In this blog post I would explain setting up integration to Marketo application using SAP PI.

 

Background:

Marketo is a popular marketing platform used in many companies. It has out of the box Salesforce integration capability for all the standard objects. However, at times it is needed to create a complex custom objects in Marketo for reporting and decision making purposes which requires custom integrations to be built using a middleware platform.

 

In this case, we are going to use SAP PI as middleware platform to sync data from DataWarehouse table where the required data is prepared to the Marketo Custom Objects (tables). In seperate blog post, I will be touching on the whole solution design. This involves, SAP BODS to bring data from Salesforce to DataWarehouse and preparing the data.

 

Available Integrations in Marketo:

Marketo has 2 integrations patters to chose from:

1. SOAP API
   
   
   
   • Use SOAP API for real time data sync (Insert/Update/Upsert/Delete) operations
   
   
   
   
   
   


2. REST API
   
   
   
   • Use for querying the Objects using Filter criteria
   
   
   • Use for Batch job scheduling for retrieving high volumes in a series of steps
   
   
   
   

In this blog we will use SOAP API to connect to Marketo and sync our data.

 

How to get the WSDL file for our SOAP API:

• Navigate to path: Admin->Web Services


• Copy the end point


• <SOAP API Endpoint> + ?WSDL -> Opens the WSDL for your Marketo Account

More information can be found at : http://developers.marketo.com/soap-api/

TIPS: If the WSDL file has the xml tag <standalone="no"> you may have to take it off before loading it into your PI system.

 

Operations to use for Custom Objects:

• For Insert/Update/Upsert we have to use "SyncCustomObjectsRequest' and "SyncCustomObjectsResponse" methods of the WSDL to create your Marketo Service Interface for Insert/Update/Upsert.

 

• For Delete we have to use "DeleteCustomObjectsRequest" and "DeleteCustomObjectsResponse" mehods of the WSDL to create Marketo Service Interface for Delete

 

Message Mappings:

Every Custom Object in Marketo has 1 or more Primary key fields on which the object is built. Rest of the fields fall under the attributes section.

• Map the fields as per the type, key fields under the "customObjKeyList"


• Map the rest of the fields under the "customObjAttributeList"

 

 

map operation - 'UPSERT'

 

Java mapping for SOAP envelope:

Steps can be found in the below marketo link,

https://developers.marketo.com/soap-api/authentication-signature/

Following Java mapping is tested and working so you can as well use the same for your Marketo SOAP envelope:

import java.io.BufferedReader;



import java.io.ByteArrayInputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.InputStream;

import java.io.InputStreamReader;

import java.io.OutputStream;

import java.io.Reader;

import java.lang.Object;



import javax.crypto.Mac;

import javax.crypto.spec.SecretKeySpec;

import javax.xml.parsers.DocumentBuilder;

import javax.xml.parsers.DocumentBuilderFactory;



import org.apache.commons.codec.binary.*;

import org.w3c.dom.Document;

import org.w3c.dom.Node;

import org.w3c.dom.NodeList;



import com.sap.aii.mapping.api.AbstractTransformation;

import com.sap.aii.mapping.api.DynamicConfiguration;

import com.sap.aii.mapping.api.DynamicConfigurationKey;

import com.sap.aii.mapping.api.InputAttachments;

import com.sap.aii.mapping.api.InputHeader;

import com.sap.aii.mapping.api.InputParameters;

import com.sap.aii.mapping.api.InputPayload;

import com.sap.aii.mapping.api.OutputAttachments;

import com.sap.aii.mapping.api.OutputHeader;

import com.sap.aii.mapping.api.OutputParameters;

import com.sap.aii.mapping.api.OutputPayload;

import com.sap.aii.mapping.api.StreamTransformationException;

import com.sap.aii.mapping.api.TransformationInput;

import com.sap.aii.mapping.api.TransformationOutput;

import com.sap.aii.mapping.lookup.Channel;

import com.sap.aii.mapping.lookup.LookupService;

import com.sap.aii.mapping.lookup.Payload;

import com.sap.aii.mapping.lookup.SystemAccessor;

import com.sap.aii.mapping.value.api.IFIdentifier;

import com.sap.aii.mapping.value.api.ValueMappingException;

import com.sap.aii.mapping.value.api.XIVMFactory;

import com.sap.aii.mapping.value.api.XIVMService;



import java.security.InvalidKeyException;

import java.security.NoSuchAlgorithmException;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

import java.util.*;



public class MarketoSOAPEnvelope extends AbstractTransformation {

	String sessionId = "";

	String SFDCserverUrl = "";

	DynamicConfigurationKey KEY = DynamicConfigurationKey.create(

			"http://sap.com/xi/XI/System/REST", "access_token");

	DynamicConfigurationKey KEY1 = DynamicConfigurationKey.create(

			"http://sap.com/xi/XI/System/REST", "serverUrl");

	// DynamicConfiguration conf = argIn.getDynamicConfiguration();



	String prefix = "<soapenv:Envelope xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:mkt=\"http://www.marketo.com/mktows/\">"

			+ "<soapenv:Header> "

			+ "<mkt:AuthenticationHeader>"

			+ "<mktowsUserId>";

	String suffix = "</mkt:AuthenticationHeader>    </soapenv:Header>   <soapenv:Body>";

	String envelope = "</soapenv:Body> </soapenv:Envelope>";



	// String sobject =

	// "</urn:sObjects xsi:type=\"Account\" xmlns=\"urn:enterprise.soap.sforce.com\">";

	/*

	 * Each JAVA Mapping using the 7.1 API will implement the method

	 * transform(TransformationInput arg0, TransformationOutput arg1) as opposed

	 * to execute(inputStream in, outputStream out) Method in earlier version.

	 */



	public void transform(TransformationInput arg0, TransformationOutput arg1)

			throws StreamTransformationException {



		/*

		 * An info message is added to trace. An instance of trace of object is

		 * obtained by calling the getTrace method of class

		 * AbstractTransformation

		 */



		// getTrace().addInfo("JAVA Mapping RemoveBodyTag is Initiated");



		/*

		 * Input payload is obtained by using

		 * arg0.getInputPayload().getInputStream()

		 */

		String signature = "";

		String marketoUserId = arg0.getInputParameters().getString("userID");

		String marketoSecretKey = arg0.getInputParameters().getString("clientKey");

		DateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ssZ");

		String text = df.format(new Date());

		String requestTimestamp = text.substring(0, 22) + ":" + text.substring(22);

		System.out.println(requestTimestamp);

		String encryptString = requestTimestamp + marketoUserId ;

		System.out.println(encryptString);

		SecretKeySpec secretKey = new SecretKeySpec(marketoSecretKey.getBytes(), "HmacSHA1");

		try{

			Mac mac = Mac.getInstance("HmacSHA1");

			mac.init(secretKey);

			byte[] rawHmac = mac.doFinal(encryptString.getBytes());	

//			char[] hexChars = Hex.encodeHex(rawHmac);

//			signature = rawHmac.toString();

			StringBuilder sb = new StringBuilder();

		    for (byte b : rawHmac) {

		        sb.append(String.format("%02X", b));

		    }

		    

			//char[] hexChars = Hex.encodeHex(rawHmac);

			//char[] hexChars = rawHmac.toString().toCharArray();

			

			signature = sb.toString();//new String(rawHmac); 

			signature = signature.trim().toLowerCase();

			

		}

		catch(NoSuchAlgorithmException ex_1){

			getTrace().addInfo("NoSuchAlgorithmException: failed");

		}

		catch(InvalidKeyException ex_2){

			getTrace().addInfo("InvalidKeyException: failed");

		}

		String authHeader = marketoUserId + "</mktowsUserId>";

		signature.replaceAll("\\s+","");

		authHeader = authHeader + "<requestSignature>"

					+ signature + "</requestSignature>"

					+ "<requestTimestamp>" + requestTimestamp + "</requestTimestamp>";

		

		String inputPayload = convertInputStreamToString(arg0.getInputPayload()

				.getInputStream());

		String outputPayload = "";		

		String temp = inputPayload;	

		outputPayload = prefix + authHeader + suffix + temp + envelope;



		try {



			/*

			 * Output payload is returned using the TransformationOutput class

			 * arg1.getOutputPayload().getOutputStream()

			 */



			arg1.getOutputPayload().getOutputStream().write(

					outputPayload.getBytes("UTF-8"));

		} catch (Exception exception1) {

		}		

	}

	



	public String convertInputStreamToString(InputStream in) {

		StringBuffer sb = new StringBuffer();



		try {

			BufferedReader reader = new BufferedReader(

					new InputStreamReader(in));



			// System.out.println("Reading File line by line using BufferedReader");

			String line = reader.readLine();



			while (line != null) {

				// System.out.println(line);

				sb.append(line);

				line = reader.readLine();

			}



		} catch (Exception ex) {



		}



		return sb.toString();

	}

	

	

	/* (non-Javadoc)

	 * @see java.lang.Object#clone()

	 */

	@Override

	protected Object clone() throws CloneNotSupportedException {

		// TODO Auto-generated method stub

		return super.clone();

	}



	/* (non-Javadoc)

	 * @see java.lang.Object#equals(java.lang.Object)

	 */

	@Override

	public boolean equals(Object obj) {

		// TODO Auto-generated method stub

		return super.equals(obj);

	}



	/* (non-Javadoc)

	 * @see java.lang.Object#finalize()

	 */

	@Override

	protected void finalize() throws Throwable {

		// TODO Auto-generated method stub

		super.finalize();

	}



	/* (non-Javadoc)

	 * @see java.lang.Object#hashCode()

	 */

	@Override

	public int hashCode() {

		// TODO Auto-generated method stub

		return super.hashCode();

	}



	/* (non-Javadoc)

	 * @see java.lang.Object#toString()

	 */

	@Override

	public String toString() {

		// TODO Auto-generated method stub

		return super.toString();

	}







	/*

	 * ****************************************************************************

	 * The below Classes and Main method are implemented to help in the stand

	 * alone testing of the mapping program. You can delete the below when

	 * exporting and creating the imported archive in SAP PI.

	 * *******************

	 * *********************************************************

	 */



	// Implementation of the main method is for the stand alone testing of the

	// mapping program

	public static void main(String args[]) throws StreamTransformationException {

		MarketoSOAPEnvelope object = new MarketoSOAPEnvelope();

		try {

//			String path = "C:\\";

//			path = path.replace("\\", "/");

			File file = new File("<path>");

			

			System.out.println(file.getAbsolutePath());

			InputStream in = new FileInputStream(file);

			OutputStream out = new FileOutputStream(new File(

					"<path>"));



			InputPayloadImpl payloadInObj = new InputPayloadImpl(in);

			TransformationInputImpl transformInObj = new TransformationInputImpl(

					payloadInObj);



			OutPayloadImpl payloadOutObj = new OutPayloadImpl(out);

			TransformationOutputImpl transformOutObj = new TransformationOutputImpl(

					payloadOutObj);



			object.transform(transformInObj, transformOutObj);

		} catch (FileNotFoundException e) {

			// TODO Auto-generated catch block

			e.printStackTrace();

		}

	}

}



/*

 * The below classes (InputPayloadImpl, TransformationInputImpl, OutPayloadImpl,

 * TransformationOutputImpl)are used to assist in the stand alone test

 */



class InputPayloadImpl extends InputPayload {

	InputStream in;



	public InputPayloadImpl(InputStream in) {

		this.in = in;

	}



	@Override

	public InputStream getInputStream() {

		return in;

	}

}



class TransformationInputImpl extends TransformationInput {



	InputPayload payload;



	public TransformationInputImpl(InputPayload payload) {

		this.payload = payload;

	}



	@Override

	public InputAttachments getInputAttachments() {

		// TODO Auto-generated method stub

		return null;

	}



	@Override

	public InputHeader getInputHeader() {

		// TODO Auto-generated method stub

		return null;

	}



	@Override

	public InputParameters getInputParameters() {

		// TODO Auto-generated method stub

		return null;

	}



	@Override

	public InputPayload getInputPayload() {

		return payload;

	}



}



class OutPayloadImpl extends OutputPayload {

	OutputStream ou;



	public OutPayloadImpl(OutputStream ou) {

		this.ou = ou;



	}



	@Override

	public OutputStream getOutputStream() {

		// TODO Auto-generated method stub

		return ou;

	}

}



class TransformationOutputImpl extends TransformationOutput {



	OutputPayload payload;



	public TransformationOutputImpl(OutputPayload payload) {

		this.payload = payload;

	}



	@Override

	public void copyInputAttachments() {

		// TODO Auto-generated method stub



	}



	@Override

	public OutputAttachments getOutputAttachments() {

		// TODO Auto-generated method stub

		return null;

	}



	@Override

	public OutputHeader getOutputHeader() {

		// TODO Auto-generated method stub

		return null;

	}



	@Override

	public OutputParameters getOutputParameters() {

		// TODO Auto-generated method stub

		return null;

	}



	@Override

	public OutputPayload getOutputPayload() {

		// TODO Auto-generated method stub

		return payload;

	}



}

Operation mapping :

I implemented a NW BPM to first map the source structure (from DB) to the Target Marketo structure and initiated the call to Marketo. In the call to Marketo, I used the java mapping to create the Authentication header required for SOAP call.

NW BPM can be avoided by using a 2 step Operation mapping. 1st step to map to Marketo structure and the second for the SOAP envelope.

 

Parameters for the Java mapping can be found under the following link in Marketo: Admin->Web Services->SOAP API. The 2 parameters that are required as inputs are:

clientKey - Encryption Key

userID - User ID

Some Important tips : Try to keep the sync volumes to under 1000 recs per sync. I used 500 at a time as the sync volume and repeated until all records in the DB are updated.

 

Delete also can be setup similarly. The java mapping can be reused for Delete also.

 

Hope this write up will save some time for those who are looking to integrate Marketo applicaiton using SAP PI as middleware. Please leave your valuable feedback.

Thanks,

Vijayanand.