Using mapping scripts in a data connection

When performing an import or export using an Excel, ServiceNow, SQL, or Dashboard connection, it may be needed to use mapping scripts.  For example, when you run into data type mismatch errors upon the import or export. A mapping script can map values from the external source to another value in Enterprise Studio, or the other way around.

You cannot directly manipulate the model in the mapping script when importing, just map values. You can access the model, but not the object being created for that line in the imported file, nor other columns. For example, you cannot create a mapping script that concatenates some columns to form the name of the object being imported, but you can look up existing objects with the same name in the model and then map the newly imported name to something else (e.g. adding a sequence number or something to disambiguate names).

A mapping script can be included by attaching a script file to the mapping relation properties. In a Dashboard connection, you can also include a mapping script text.

Example of a mapping script text at the export properties of a Dashboard mapping relation

In a mapping script, the "value" refers to the value that is read from the input file when importing, or from the model when exporting. The "output" refers to the mapped values in the model when importing, and the output file when exporting.

// Convert RTF to String (mapping from a documentation field)
output value.toString();

// Convert Yes/No values to mandatory/optional
if (value == "Yes") { output "mandatory"; } 
if (value == "No") { output "optional"; } 

// Convert value range to String
if (value == "1") { output "one"; } 
if (value == "0..1") { output "one"; } 
if (value == "0..*") { output "many"; } 
if (value == "1..*") { output "many"; }

// Convert String to Integer
if ( value.isNumberFormat() ) { // Is String in Number Format
if ( value =="0" ) { myValue = 0;}
else if ( value == "1" ) { myValue = 1; }
else if ( value == "2" ) { myValue = 2; }
else if ( value == "5" ) { myValue = 5; }
else if ( value == "6" ) { myValue = 6; }
else if ( value == "7" ) { myValue = 7; }
else if ( value == "8" ) { myValue = 8; }
else { myValue = 9; }
myValue = myValue.toInteger();
output myValue;
stop;
}
myValue = -1;
output myValue.toInteger();