Core Concepts

CONVAYR allows you to integrate data from one application, the source, to a target application. This is called a Connection.

Once you have selected your source and target applications and authorised the applications, you define the way in which the data is transferred by defining a mapping.

The mapping allows you to select how each piece, or field, of the source data is mapped into the target system. This can be as simple as selecting the target field that you want the data transferred to. Mappings can have multiple sections, so the source data can for example create or update multiple objects in the target system. You can also transform the source value by defining a value transform and define conditions on a per field or per section basis

Value transforms include date transformations, picklist mappings e.g. when value A use value B, and scripting. Scripting is covered in the section below JEXL Script

Once you have defined your connection you can run it manually or define a schedule to run it on easily defined intervals.

You can create dependancies so that one Connection causes one or more other Connections to run.

You can allow people to control Connections with emails and submit csv data as attachments.

Users can subscribe to notifications to receive emails when a Connection runs and whether there were any errors.

Detailed logs are maintained each time the Connection is run and in the event of an error you can simply correct the mapping, or data, and re-run it.

Batch Processing

CONVAYR makes use of batch processing when it is available in a target system. This is the most efficient and therefore quickest method of running a job. Not only does CONVAYR make less calls to the target system, but also where it can, de-duplicates the payload. The savings on large payloads are substantial.

You have the ability to disable batch processing, but should only do this if you really need to. The only circumstances where this makes sense are:

CONVAYR IP Addresses

CONVAYR is hosted in 2 physical locations the addresses for the loadbalancers are:

CONVAYR traffic can originate from either IP address. You need to allow traffic on your system from both of these addresses.

Target File Date Pattern

It is not uncommon to employ a naming convention for data files that includes the current date/time in the file name. CONVAYR supports this by allowing you to specify a date pattern. An example of a valid date pattern is:

The '%d{}' surrounds the pattern itself. So the above pattern will match a file with the current day, month and year in the name e.g. 01-12-2019.csv will be matched when the job runs on the 1st December 2019.

A full description of the date formats supported by CONVAYR is available here https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

JEXL Script

You can use scripts to transform source data and create values for use in the mapping. The scripts are based on JEXL and allow complex transformations of source values as well as allowing access to runtime data e.g. row index number

This is extremely powerful and allows, amongst other things, string manipulation to transform the incoming source value.

There are three objects available in the scripts: $src (the source value), $tgt (the target value) and $ctx (the context).

To represent a standard non-transformed mapping the script would be

var v = $src.val
$tgt.val = v

or even more simply

$src.val

because the transformed value is either the value of the whole script (an expression) or the value that $tgt.val is set to within the script.

The value can then be further transformed using Java String functions. So to replace spaces with underscores you could use:

$src.val.replace(' ', '_')

To convert the value to lower case you could use

$src.val.toLowerCase()

To limit the length of the value to ten characters

var v = $src.val
if(v.length() > 10) {
  v = v.substring(0, 9)
}
$tgt.val = v

The following is a more complex script to change the email domain from the value in the source(abd.com) to another value (abc.com)

var v = $src.val
var toks = v.split('@')
if(toks.size() > 1) {
  if("abd.com".equals(toks[1])) {
    v = toks[0]+"@abc.com"
  }
}
$tgt.val = v

You can also manipulate dates. The following classes are available:

• DateTimeFormatter
• LocalDate
• LocalDateTime
• ZonedDateTime
• Duration
• Period

This example adds 6 months to the source date time

var dateFormat = DateTimeFormatter:ofPattern("yyyy-MM-dd HH:mm:ss")
var date = LocalDateTime:parse($src.val, dateFormat).plusMonths(6)
$tgt.val = date.format(dateFormat)

If the source value was a date not a date & time you could do the following

var dateFormat = DateTimeFormatter:ofPattern("yyyy-MM-dd")
var date = LocalDate:parse($src.val, dateFormat).minusYears(1)
$tgt.val = date.format(dateFormat)

To change the target date format you would do the following

var dateFormatIn = DateTimeFormatter:ofPattern("yyyy-MM-dd'T'HH:mm:ssX")
var date = LocalDateTime:parse($src.val, dateFormatIn).plusYears(1)
var dateFormatOut = DateTimeFormatter:ofPattern("yyyy-MM-dd HH:mm:ss")
$tgt.val = date.format(dateFormatOut)

To calculate the number of days from the source date (dd-mm-yyyy) to a fixed end date (31-12-2024)

var dtf = DateTimeFormatter:ofPattern("dd-MM-yyyy")

var startDate = LocalDate:parse($src.val, dtf).atStartOfDay()
var endDate = LocalDate:parse("31-12-2024", dtf).atStartOfDay()

$tgt.val = Duration:between(startDate, endDate).toDays()

This example calculates the largest number from 2 source values. Although you have to select a source field for the transform, you do not necessarily use the value.

var fields = $src.fields

var largest = 0

var n1_str = fields.get('number 1')

if(!n1_str.equals("")) {
  var n1 = Integer:parseInt(n1_str)
  if(n1 > largest) {
    largest = n1
  }
}
var n2_str = fields.get('number 2')
if(!n2_str.equals("")) {
  var n2 = Integer:parseInt(n2_str)
  if(n2 > largest) {
    largest = n2
  }
}
$tgt.val = largest

The error() and info() methods available against $ctx allow the script to both write to the log and to throw an error. This example throws an error if an email is empty. This is extremely useful for automating data validation.

var email = $src.val

if(email.equals("")) {
  $ctx.error = 'ERROR: Empty email'
}
$tgt.val = email

These are the top level objects and methods:

$src

$tgt

$ctx

Email Run

You can run connections in CONVAYR via email by providing the source data as a csv file attachment. This allows people who are not registered CONVAYR users to run a Connection.

Email Run must be switched on for both the account and the connection, and the email of the external user must be added to the permitted emails list. This email will be checked when a run request is received.

The run email is of the form abc123@conveyor-run.frogfoundry.com, so it is a good idea to add it to your address book and give it a more meaningful title e.g. Run Contacts Import <abc123@conveyor-run.frogfoundry.com>

There is an additional configuration option to allow you to set a maximum size for the csv file. This can be Unlimited (default) or up to 1000 in increments of 10.

The template (headers) for the csv file can be obtained from Activation->Run Now->Download CSV Headers.

An email response will be returned for a run request from a valid email.

Salesforce Bulk API Query

SOQL queries are by default run using the Bulk API. You should in most case use the Bulk API, there are however differences in how the APIs deal with certain types of SOQL queries.

Binary (base64) fields cannot be included in the fields of a bulk query. Additionally TYPEOF, which is used to retrieve fields of polymorphic relationships is not supported

CONVAYR supports the mapping of binary (base64) data from source to target systems. Turning Bulk API off in this case will allow CONVAYR to retrieve the base64 fields.

Eloqua App

CONVAYR uses OAuth to authenticate against your Eloqua instance. An Administrator needs to set up an App to allow this.

You need to navigate to Settings - Extensions - AppCloud Developer and create an App.

Use the following URLs

You will need the Client ID and Client Secret from the new App to setup the OAuth connection in CONVAYR.