Orchestrator Integration Pack for Data Manipulation

Introduction

The Orchestrator Integration Pack for Data Manipulation enables you to perform a wider variety of data manipulation tasks as well as aggregate data into common activity for enhanced processing.

This Integration Pack adds the following activities to System Center Orchestrator:

• Aggregate Data
• Compare Multiple Values
• Expand Fields
• Join Fields
• Match Pattern
• Replace Text
• Split Fields
• Transform Data

Download the latest version here.

System Requirements

• System Center 2012 Orchestrator
• Microsoft .NET Framework 3.5 SP1

Installation

Register and Deploy the Integration Pack using the Deployment Manager application as outlined in the How to Install an Integration Pack article on TechNet.

Inline Data Manipulation Functions

Inline data manipulation functions may be used to parse the data of any property field of any of the activities prior to executing the activity functionality.

Inline data manipulation functions are identified by their call wrapper of “<@>” (e.g. <@Len(‘my text’)>) and may be nested inside other function calls (e.g. <@Len(‘<@Left(‘my text’,’2’)>’)> would see the “Left” function return the text “my” and then the “Len” function would process that output to produce a result of “2”.  Function names are not case sensitive.

Arguments are passed to functions by encapsulating them in between two identifiers which can be apostrophe characters (e.g. <@Len(‘my text’)>) or quotes (e.g. <@Len(“my text”)>).  If no identifiers are provided (e.g. <@Left(my text,2)>) then the data manipulation function will do its best to try and figure out where one argument stops and another begins, however unexpected data may result such as trimming of leading and trailing spaces or premature separation of arguments (e.g. if text that contains a comma is included then that comma will be interpreted as an argument separation).

The table below outlines the available inline data manipulation functions:

Function	Description	Examples
DateDiff	Subtracts the first timestamp provided from the second timestamp provided and returns the difference in days, hours, minutes or seconds.	<@DateDiff(‘12/31/2008’,’01/01/2009’,’days’)> - returns 1. <@DateDiff(‘12/31/2008’,’01/01/2009’,’seconds’)> - returns 86400. <@DateDiff(‘01/01/2009’,’12/31/2008’,’seconds’)> - returns -86400.
Diff	Subtracts the second argument from the first argument.  Optionally a precision value to identify the number of decimal places in the result.	<@Diff(10,5)> - returns ‘5’. <@Diff(6.666, 3.333,2)> – returns ‘3.33’.
Div	Divides the first argument by the second argument.  Optionally a precision value to identify the number of decimal places in the result.	<@Div(10,5)> - returns ‘2’. <@Div(10,3,2)> – returns ‘3.33’.
Field	Parses a string of text to split into separate fields based on a delimiter that is matched using standard or optionally regular expression search criteria and then returns the identified field.	<@Field(‘one;two;three’,’;’,2)> - returns ‘two’. <@Field(‘one|two|three’,’\|’,2,true)> ‘two’.
FindPattern	Searches a string for a regular expression and returns the specified match if found.	<@FindPattern(‘abcdefghi’,’(abc|def|ghi)’,2)> - returns “def”.
Format	Formats text of either date or numeric values and defines how to present the output.	<@Format(‘12/31/2008’, ‘yyyy-MM-dd’)> – returns ‘2008-12-31’. <@Format(’12.3456’,’###0.00’)> - returns 12.35 (note the rounding when shrinking the precision).
If	Performs a comparison of two fields against a defined operator and returns response text based on either a true or false result.  Operators are: =, <, >, <=, >= and <>.  Comparisons are processed as strings by default with an option to set comparison to “number” (see examples).	<@If(=, ‘test’,’test’,’success’,’failed’) returns “success”. <@If(>,’64’,’8’,’success’,’failed’,’number’)> returns “success” <@If(>,’64’,’8’,’success’,’failed’)> returns “failed” (comparison processed as string)
InStr	Parses a string to determine if a substring exists within and if so, returns the character index where the substring starts.	<@InStr(‘one;two;three’,’two’)> returns 5.
Left	Extracts a defined number of characters of a string starting at position 1.	<@left(‘abcdef’,3)> returns “abc”.
Len	Determines the length of a string.	<@Len(‘abcdef’)> returns “6”.
Lower	Converts all alphabetic characters in a string to lowercase.	<@Lower(‘ABCDEF’)> returns “abcdef”.
LTrim	Removes any leading spaces from a string.	<@LTrim(‘    abcdef    ’)> returns “abcdef    “.
Mid	Extracts a defined number of characters of a string starting at a defined position.	<@Mid(‘abcdef’,3,2)> returns “cd”.
Mult	Multiplies each argument with the previous argument.	<@Mult(5,5,10,2)> returns “500”.
PadLeft	Prefixes a string with spaces or an optionally defined character until the string equals the total defined size.	<@PadLeft(‘text’,10)> returns “      text”. <@PadLeft(‘text’,10,’0’> returns “000000text”.
PadRight	Suffixes a string with spaces or an optionally defined character until the string equals the total defined size.	<@PadRight(‘text’,10)> returns “text      “. <@PadRight(‘text’,10,’0’)> returns “text000000”.
Replace	Searches a string for a defined substring or optionally applies a regular expression and then replaces the substring with defined text.	<@Replace(‘abc|def’,’|’,’123’)> returns abc123def. <@Replace(‘abc123def’,’[0-9][0-9][0 returns “abc|def”.
Right	Extracts a defined number of characters of a string starting at the length of the string.	<@Right(‘abcdef’,3)> returns “def”.
RTrim	Removes any trailing spaces from a string.	<@RTrim(‘    abcdef    ‘)> returns “    abcdef”.
SortFields	Parses a string of text to split into separate fields based on a delimiter that is matched using search criteria and then returns the fields sorted in ascending or optionally descending order(options are “asc”, “desc”, “ascending” and “descending”).	<@SortFields(‘one;two;three;four’,’;’)> returns “four;one;three;two”. <@SortFields(‘one;two;three;four’,’;’,’desc’)> returns “two;three;one;four”.
Sum	Adds each argument with the previous argument.	<@Sum(5,5,10,2)> returns “22”.
Trim	Removes any leading and trailing spaces from a string.	<@Trim(‘    abcdef    ‘)> returns “abcdef”.
Upper	Converts all alphabetic characters in a string to uppercase.	<@Upper(‘abcdef’)> returns “ABCDEF”.

Aggregate Data

The Aggregate Data activity provides a single activity point to collect and manipulate multiple input strings resulting in a single set of data based upon multiple varying input points.  Please note that this activity does not aggregate data across multiple data pipes (e.g. a Read Line activity that returns 10 pipes of different lines will not be able to aggregate those 10 pipes into 1 however multiple Read Line activities will be able to aggregate each of their first pipes collectively and their second pipes collectively etc.).

Properties

Input into the Aggregate Data activity consists of 50 separate input strings identified as “Input String 01” through “Input String 50”.  Input String 01 is a required property with Input String 02 through Input String 50 being optional.

Published Data

In addition to each Input String XX being provided as Published Data, there is a corresponding Output String 01 through Output String 50 that returns the text results of the inline data manipulation processing.

Compare Multiple Values

The Compare Multiple Values activity allows you to perform a series of comparisons all within one activity and then act upon the results of each comparison individually or apply a formula against all or a select group of the comparisons to return an overall result.

Properties

Input into the Compare Multiple Values activity consists of multiple groupings of properties that collectively represent a single comparison operation.  These properties are recognized by their “Value XX <property>” name (e.g. Value01 Compare Type).

Value01 <property> properties are required fields while Value02 <property> properties through Value20 <property> properties are optional.  In order to effectively use one of the Value02 through Value20 properties, the entire grouping must be used.

Each Value XX grouping contains four properties:

• ValueXX Compare Type – Type of comparison to perform.  Options are “Number”, “String (Case Insensitive)” and “String (Case Sensitive)”.  Selecting “Number” performs the compare operation in a numeric manner.  Selecting either “String” value performs the compare operation in a text manner with “String (Case Insensitive)” performing the comparison without taking text case into consideration.
• ValueXX Compare Operator – The operation to perform.  Options are “Equals”, “Does Not Equal”, “Matches Pattern” (only available against String compare types), “Does Not Match Pattern” (only available against String compare types), “Less Than”, “Less Than or Equals”, “Greater Than” and “Greater Than or Equals”.  “Matches Pattern” and “Does Not Match Pattern” utilize regular expressions to perform the comparison.
• ValueXX Input 1 – The value to compare.
• ValueXX Input 2 – The value to compare against or if using “Matches Pattern” or “Does Not Match Pattern” Compare Operator, the regular expression to match.

In addition, there are two individual properties:

• Comparison Formula – Uses the “ValueXX” monikers to construct a formula to be evaluated (e.g. ((Value01 AND Value02) OR Value03) would return a “true” result if either Value01 and Value02 both returned “true” or if Value03 returned “true”.
• Comparison Errors Handled As – Determines how errors in any of the comparisons should be handled.  Options are “Object Failure” which causes the Compare Multiple Values activity to return a “Failed” status and “Result Failure” which causes the specific “ValueXX” group comparison result to be “false”.

Published Data

In addition to the values of each property being provided as Published Data, the following Published Data elements are available:

• Comparison Formula Result – Result of the evaluation of the Comparison Formula (true or false).
• ValueXX Result – Result of the ValueXX comparison operation (true or false).

Expand Fields

The Expand Fields activity provides the ability to convert multiple delimited fields of input into correlated sets of Published Data.

Example:

Input String 01 = “one;two;three;four;five”
Input String 02 = “a;b;c;d;e”

Resulting Published Data:

Set #	Output String 01	Output String 02
1	one	a
2	two	b
3	three	c
4	four	d
5	five	e

Properties

• Split Delimiter – The character, string of characters or regular expression to be matched to determine where the string splitting occurs.
• Use Regular Expression – Whether or not to apply the pattern matching using Regular Expression.

All additional properties consist of 50 separate input strings identified as Input String 01 through Input String 50.  Input String 01 is a required property with Input String 02 through Input String 50 being optional.

Published Data

In addition to the values of each input property being provided as Published Data, there is a corresponding Output String 01 through Output String 50 to Input String XX that returns the text results of each data set generated from the input data.

Join Fields

The Join Fields activity provides the ability to join up to 50 strings of text together by a defined delimiter.  While functionally no different than using {PD from Activity1}<delimiter>{PD from Activity2} in any desired location, this provides a more user friendly way to manage large amounts of text or create a string that will be utilized in several other activities down the pipeline without having to rebuild it each time.

Properties

• Join Delimiter – The character or string of characters that will be inserted between each field.
• FieldXX – A series of 50 properties to be used as input strings to build the result.  Field01 and Field02 are required properties while Field03 through Field50 are optional.

Published Data

In addition to the values of each input property being provided as Published Data, the following Published Data elements are available:

• Fields Used – The number of fields leveraged to generate the Joined String calculated based on the highest FieldXX property populated with data.
• Joined String – The result of all the FieldXX properties joined together by the Join Delimiter property.

Match Pattern

The Match Pattern object provides the ability to apply a Regular Expression against an input string and return one or all matches to the Regular Expression found in the string.

Properties

• Input String – The string to be pattern matched.
• Pattern – The pattern to match in the Input String.
• Case Sensitive – Whether or not the pattern matching should be case sensitive.
• Return Match Number – Which match number to be returned (0 = all matches).

Published Data

In addition to the values of each property being provided as Published Data the following Published Data elements are available:

• Match Result – Overall result of the Pattern matching activity (success or failed).
• Match String – The text of the match to the Pattern in the Input String.
• Matches Found – The total number of matches to the Pattern found in the Input String.

Replace Text

The Replace Text object provides the ability to apply a standard or Regular Expression pattern against an input string and replace all occurrences of that pattern with defined text.

Properties

• Input String – The string to be pattern matched.
• Pattern – The pattern to match in the Input String.
• Replacement Text – The defined text to replace any pattern matches.
• Use Regular Expression – Whether or not to apply the pattern matching using Regular Expression.

Published Data

In addition to the values of each property being provided as Published Data the following Published Data elements are available:

• Output String – Contents of the Input String with all Pattern matches replaced by Replacement Text.
• Replace Result – Overall result of the replacement of matched Pattern text to Replacement Text.

Split Fields

The Split Fields object provides the ability to split an Input String into multiple fields based on a specified character or string of characters applied by matching either a standard or Regular Expression pattern.

Properties

• Input String – The string to be pattern matched and split.
• Split Delimiter – The character, string of characters or regular expression to be matched to determine where the string splitting occurs.
• Use Regular Expression – Whether or not to apply the pattern matching using Regular Expression.

Published Data

In addition to the values of each property being provided as Published Data the following Published Data elements are available:

• FieldXX – The fields as they are split from the original Input String. The object supports up to 50 fields to be published. If more than 50 splits are made, the object will only return the first 50 as Published Data.
• Fields Used – The number of fields populated by the splitting activity.

Transform Data

The Transform Data object provides the ability to dynamically configure the Properties and Published Data elements of the object based on the contents of an XML based transformation configuration file and apply data transformations to translate a complete set of data from one representation to another and maintain fieldnames that are consistent with both the data source and the data destination.

Properties

All properties are dynamically generated based on the contents of the Transform Configuration File property in Tranform Data Configuration type of Data Manipulation options.

Published Data

All Published Data is dynamically generated based on the contents of the Transform Configuration File property in Tranform Data Configuration type of Data Manipulation options.

Transform Configuration File

Field Attributes

Please note that Field tags and all attributes are case sensitive.

• InputName – Name to be displayed as a required or optional property in properties.
• OutputName – Name to be displayed as Published Data when selecting Published Data from the object.
• Output Description – Description of the field to be displayed along with a reference to the InputName attribute when selecting Published Data from the object
• Type – Identifies the field type that should be published. Valid values are “DateTime”, “Number” and “String”.
• Required – (Optional) Identifies whether the InputName should be displayed as a required or optional property. Valid values are “True” and “False”. Default value is “False” if attribute is not included.

Available Transformations

Please note that Transform tags and all attributes are case sensitive.

• DateAdd – Used to add a specified number of days, hours, minutes or seconds to a datetime field.
  • Syntax: <DateAdd Days=”1” Hours=”1” Minutes=”1” Seconds=”1” />
• Format – Used to format a number or date to a specified format.
  • Syntax: <Format OutputFormat=”MM/dd/yyyy HH:mm:ss” />
• LowerCase – Used to convert all characters in a given property to lowercase.
  • Syntax: <LowerCase />
• LTrim – Used to remove leading spaces from a given property.
  • Syntax: <LTrim />
• PadLeft – Used to pad a given property to the left to produce a string of a specified size adding an optionally specified character.
  • Syntax: <PadLeft Size=”10” Character=”X” />
• PadRight – Used to pad a given property to the right to produce a string of a specified size adding an optionally specified character.
  • Syntax: <PadRight Size=”10” Character=”X” />
• Replace – Used to locate a specified string or regular expression within a given property and replacing it with specified text.
  • Syntax: <Replace Method=”RegEx” SearchString=”(abc|def)” Output=”123” />
• Trim – Used to apply both LTrim and RTrim transformations to a given property.
  • Syntax: <Trim />
• UpperCase – Used to convert all characters in a given property to uppercase.
  • Syntax: <UpperCase />

File Format

The Transform Configuration File is formatted as an XML file with the following requirements:

1. All tags and attributes are case sensitive.
2. A root node of “<Fields></Fields>” must be present, and can only exist once.
3. Individual fields are identified using a tag of “<Field></Field>”.
4. Transform lists are identified using a tag of “<Transform></Transform>”.

Example Transform Configuration File:

<Fields>
     <Field InputName="Priority" OutputName="Severity" OutputDescription="Severity of the incident." Type="String" Required="TRUE">
          <Transform>
               <Replace SearchString="Critical" Output="1" />
               <Replace SearchString="High" Output="2" />
               <Replace SearchString="Medium" Output="3" />
               <Replace SearchString="Low" Output="4" />
          </Transform>
     </Field>
     <Field InputName="Contact Name" OutputName="Customer Name" OutputDescription="Name of contact." Type="String" Required="TRUE">
          <Transform>
               <UpperCase />
          </Transform>
     </Field>
     <Field InputName="Created Date" OutputName="Created Date" OutputDescription="Start date of incident." Type="DateTime" Required="TRUE">
          <Transform>
               <DateAdd Hours="5" />
               <Format OutputFormat="MM/dd/yyyy HH:mm:ss" />
          </Transform>
     </Field>
     <Field InputName="Ticket ID" OutputName="Old Ticket ID" OutputDescription="Ticket ID from old system." Type="Number">
          <Transform>
               <PadLeft Size="10" Character="0" />
          </Transform>
     </Field>
</Fields>