Task Factory users running version 2020.1.4 or older (released prior to May 27, 2020): There's an important Task Factory update. Please visit here for more details.
Abort
Description
Stops the dataflow and issues a specified error message to the dataflow log file.
Syntax
ABORT( String_Message [, Condition] )
Returns
NULL
Parameters
Name
Optional
Description
String_Message
false
The message you want to display in the dataflow log file when the dataflow stops. The string can be any length and you can enter any valid transformation expression.
Condition
false
(Optional) The conditional expression in which the data flow should be aborted.
Examples
Abort("Processing aborted"), Abort("Zero Length Detected In Name", Length(FirstName) = 0)
Abs
Description
Returns the absolute value of a numeric value
Syntax
ABS( [Numeric_Value] )
Returns
Positive Numeric value. ABS returns the same datatype as the numeric value passed in as an argument, can be NULL.
Parameters
Name
Optional
Description
Numeric_Value
false
Passes the values for which you want to return the absolute values. You can enter any valid transformation expression.
Acos
Description
Returns the angle whose cosine is the specified number.
Syntax
ACOS( Numeric_Value )
Returns
Double value. NULL if a value passed to the function is NULL.
Parameters
Name
Optional
Description
Numeric_Value
false
Numeric datatype. A number representing a cosine, where number_value must be greater than or equal to -1, but less than or equal to 1. You can enter any valid task editor.
Examples
Acos(0.5), Acos(varValue)
AddToDate
Description
Returns a specified date with the specified number interval (integer) added to a specified datepart of that date. If you add or subtract value for specified datepart.
Syntax
AddToDate( Date, Format, Amount)
Returns
Returns modified date.
Parameters
Name
Optional
Description
Date
false
valid datetime value or expression
Format
false
Valid string format or expression for datepart you want to add/subtract. Supported format values are as below Year Format => Y, YYY, YY, YYYY Month Format => M, MM, MON, MONTH Day Format => D, DD, DDD, DY, DAY Hour Format => H, HH, HH12, HH24 Minute Format => MI, N Second Format => S, SS Milisecond Format => MS
Amount
false
Any valid integer value or expression specifying number of Year, Month, Day, Hour, Minute, Second or Milisecond. If you supply positive value then it will perform add else it will do subtract.
Examples
AddToDate( ORDER_DATE, 'DD', 3 ) //Adds 3 days to ORDER_DATE and return new datetime AddToDate( ORDER_DATE, 'HH', 14 ) //Adds 14 hours to ORDER_DATE and return new datetime AddToDate( ORDER_DATE, 'MM', -5 ) //Subtract 5 months from ORDER_DATE and return new datetime AddToDate( ORDER_DATE, 'YYYY', 1 ) //Add one year to supplied ORDER_DATE and return new datetime
AesDecrypt
Description
Returns decrypted data to string format using Advanced Encryption Standard (AES) algorithm with 128-bit encoding.
Syntax
AesDecrypt( Value, Key )
Returns
Returns a decrypted binary value that can be NULL if the input value is a null value.
Parameters
Name
Optional
Description
Value
false
The Binary datatype you wish to decrypt.
Key
false
The String of precision 16 characters or fewer that was originally used to encrypt the value. You can use mapping variables for the key.
Returns data in encrypted format using Advanced Encryption Standard (AES) algorithm with 128-bit encoding.
Syntax
AesEncrypt( Value, Key )
Returns
Returns an encrypted binary value that can be NULL if the input is a null value.
Parameters
Name
Optional
Description
Value
false
The String value you wish to encrypt.
Key
false
The String of precision 16 characters or fewer that will be used to encrypt the value. You can use mapping variables for the key.
Examples
AesEncrypt("Encrypt this value", "key-value-1234"), AesEncrypt(PasswordColumn, "key-value-1234"), AesEncrypt(FileReadAllText("C:\data\unencrypted.txt"), "key-value-1234")
Asin
Description
Returns the angle whose sine is the specified number..
Syntax
Asin( Numeric_Value )
Returns
Double value. NULL if a value passed to the function is NULL.
Parameters
Name
Optional
Description
Numeric_Value
false
Numeric datatype. A number representing a sine, where number_value must be greater than or equal to -1, but less than or equal to 1. You can enter any valid task editor.
Examples
Acos(varValue)
Atan
Description
Returns the angle whose tangent is the specified number.
Syntax
Atan( Numeric_Value )
Returns
Numeric
Parameters
Name
Optional
Description
Numeric_Value
false
Passes the values for which you want to the Atan of
Examples
Atan(55)
Avg
Description
Aggregation that calculates the average of all values. Can only be used as part of the Advanced Aggregation component
Syntax
Avg( Value [, Condition])
Returns
Double.
Parameters
Name
Optional
Description
Value
false
The value to add to the aggregation
Condition
false
The condition allows you to conditionally add values to the aggregation based on the expression. For instance, you could define the aggregate as Avg(SalesTotal, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida.
Returns the smallest integer that is greater than or equal to the numeric value passed to this function.
Syntax
Ceiling( Numeric_Value )
Returns
Returns an integer if you pass a numeric value with declared precision between 0 and 28, otherwise it returns a Double. NULL values will return a NULL.
Parameters
Name
Optional
Description
Numeric_Value
false
Must be a numeric datatype. You can enter any valid transformation expression.
Choose
Description
Chooses a string from a list of strings based on a given position. You specify the position and the value. If the value matches the position, the Integration Service returns the value. You can either pass in comma delimeted static values or pass in a variable or column with delimeted values
CHR returns the ASCII character corresponding to the numeric value you pass to this function. ASCII values fall in the range 0 to 255. You can pass any integer to CHR, but only ASCII codes 32 to 126 are printable characters.
Syntax
CHR( Numeric_Value )
Returns
ASCII or Unicode character. A string containing one character. NULL if a value passed to the function is NULL.
Parameters
Name
Optional
Description
Numeric_Value
false
Numeric datatype. The value you want to return as an ASCII or Unicode character. You can enter any valid transformation expression.
Examples
Chr(65), Chr(varCharacterValue)
ChrCode
Description
ChrCode returns the numeric ASCII value of the first character of the string passed to the function. ASCII values fall in the range 0 to 255.
Syntax
ChrCode ( String )
Returns
ASCII or Unicode character. A string containing one character. NULL if a value passed to the function is NULL.
Parameters
Name
Optional
Description
String
false
Character string. Passes the values you want to return as ASCII or Unicode values. You can enter any valid task editor.
Examples
ChrCode("A"), ChrCode("?")
Concat
Description
Concatenates two strings.
Syntax
CONCAT( First_String, Second_String )
Returns
String. NULL if both string values are NULL. Nulls If one of the strings is NULL, CONCAT ignores it and returns the other string. If both strings are NULL, CONCAT returns NULL.
Parameters
Name
Optional
Description
First_String
false
Any datatype except Binary. The first part of the string you want to concatenate. You can enter any valid task editor.
Second_String
false
Any datatype except Binary. The second part of the string you want to concatenate. You can enter any valid task editor.
The existing base of the integer. Valid values are 2, 8, 10, 16
To_Base
false
The new base of the integer. Valid values are 2, 8, 10, 16
Examples
ConvertBase("1234", 10, 2)
Cos
Description
Returns the cosine of a numeric value (expressed in radians).
Syntax
Cos( Numeric_Value )
Returns
Double value. NULL if a value passed to the function is NULL.
Parameters
Name
Optional
Description
Numeric_Value
false
Numeric datatype. Numeric data expressed in radians (degrees multiplied by pi divided by 180). Passes the values for which you want to calculate a cosine. You can enter any valid task editor.
Examples
Cos(10), Cos(varValue)
Cosh
Description
Returns the hyperbolic cosine of a numeric value (expressed in radians).
Syntax
Cosh( Numeric_Value )
Returns
Double value. NULL if a value passed to the function is NULL.
Parameters
Name
Optional
Description
Numeric_Value
false
Numeric datatype. Numeric data expressed in radians (degrees multiplied by pi divided by 180). Passes the values for which you want to calculate the hyperbolic cosine. You can enter any valid task editor.
Examples
Cosh(10), Cosh(varValue)
Count (*)
Description
Aggregation that calculates the total number based on the grouping. Can only be used as part of the Advanced Aggregation component
Syntax
Count( Value [, Condition])
Returns
Integer.
Parameters
Name
Optional
Description
Value
false
The value to add to the aggregation
Condition
false
The condition allows you to conditionally add values to the aggregation based on the expression. For instance, you could define the aggregate as Count(*, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida.
Examples
Count(*), Count(*, CustomerState="FL")
Count (column name)
Description
Aggregation that calculates the total number based on the grouping. Can only be used as part of the Advanced Aggregation component
Syntax
Count( Column_Name [, Condition])
Returns
Integer.
Parameters
Name
Optional
Description
Column_Name
false
The column to retrieve the value of to add to the aggregation
Condition
false
The condition allows you to conditionally add values to the aggregation based on the expression. For instance, you could define the aggregate as Count(CustomerID, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida.
