Task Factory Functions List A-C

Abort

Abort

DescriptionStops the dataflow and issues a specified error message to the dataflow log file.
SyntaxABORT( String_Message [, Condition] )
ReturnsNULL
Parameters
NameOptionalDescription
String_MessagefalseThe 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.
Conditionfalse(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

Abs

DescriptionReturns the absolute value of a numeric value
SyntaxABS( [Numeric_Value] )
ReturnsPositive Numeric value. ABS returns the same datatype as the numeric value passed in as an argument, can be NULL.
Parameters
NameOptionalDescription
Numeric_ValuefalsePasses the values for which you want to return the absolute values. You can enter any valid transformation expression.
Acos

Acos

DescriptionReturns the angle whose cosine is the specified number.
SyntaxACOS( Numeric_Value )
ReturnsDouble value. NULL if a value passed to the function is NULL.
Parameters
NameOptionalDescription
Numeric_ValuefalseNumeric 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

AddToDate

DescriptionReturns 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.
SyntaxAddToDate( Date, Format, Amount)
ReturnsReturns modified date.
Parameters
NameOptionalDescription
Datefalsevalid datetime value or expression
FormatfalseValid 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
AmountfalseAny 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

AesDecrypt

DescriptionReturns decrypted data to string format using Advanced Encryption Standard (AES) algorithm with 128-bit encoding.
SyntaxAesDecrypt( Value, Key )
ReturnsReturns a decrypted binary value that can be NULL if the input value is a null value.
Parameters
NameOptionalDescription
ValuefalseThe Binary datatype you wish to decrypt.
KeyfalseThe String of precision 16 characters or fewer that was originally used to encrypt the value. You can use mapping variables for the key.
Examples
AesDecrypt(varEncryptedData, "key-value-1234"), AesDecrypt(EncryptedValueColumn, "key-value-1234"), AesDecrypt(FileReadAllText("C:\data\encrypted.txt"), "key-value-1234")
AesEncrypt

AesEncrypt

DescriptionReturns data in encrypted format using Advanced Encryption Standard (AES) algorithm with 128-bit encoding.
SyntaxAesEncrypt( Value, Key )
ReturnsReturns an encrypted binary value that can be NULL if the input is a null value.
Parameters
NameOptionalDescription
ValuefalseThe String value you wish to encrypt.
KeyfalseThe 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

Asin

DescriptionReturns the angle whose sine is the specified number..
SyntaxAsin( Numeric_Value )
ReturnsDouble value. NULL if a value passed to the function is NULL.
Parameters
NameOptionalDescription
Numeric_ValuefalseNumeric 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

Atan

DescriptionReturns the angle whose tangent is the specified number.
SyntaxAtan( Numeric_Value )
ReturnsNumeric
Parameters
NameOptionalDescription
Numeric_ValuefalsePasses the values for which you want to the Atan of
Examples
Atan(55)
 Avg

Avg

DescriptionAggregation that calculates the average of all values. Can only be used as part of the Advanced Aggregation component
SyntaxAvg( Value [, Condition])
ReturnsDouble.
Parameters
NameOptionalDescription
ValuefalseThe value to add to the aggregation
ConditionfalseThe 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.
Examples
Avg(SalesTotal), Avg(SalesTotal, CustomerState="FL")
 BinToDecimal

BinToDecimal

DescriptionConverts a binary value to an decimal value.
SyntaxBinToDecimal( Value )
ReturnsDecimal
Parameters
NameOptionalDescription
ValuefalseThe binary value to convert.
Examples
BinToDecimal(10)
BinToHex

BinToHex

DescriptionConverts a binary value to an hexadecimal value.
SyntaxBinToHex( Value )
ReturnsDecimal
Parameters
NameOptionalDescription
ValuefalseThe binary value to convert.
Examples
BinToHex(10)
BinToOct

BinToOct

DescriptionConverts a binary value to an octal value.
SyntaxBinToOct( Value )
ReturnsDecimal
Parameters
NameOptionalDescription
ValuefalseThe binary value to convert.
Examples
BinToOct(10)
Ceiling

Ceiling

DescriptionReturns the smallest integer that is greater than or equal to the numeric value passed to this function.
SyntaxCeiling( Numeric_Value )
ReturnsReturns 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
NameOptionalDescription
Numeric_ValuefalseMust be a numeric datatype. You can enter any valid transformation expression.
Choose

Choose

DescriptionChooses 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
SyntaxChoose( Index, String1 [, String2, ..., StringN] ), Choose( Index, Delimeted_String [, Delimeter_Value=","])
ReturnsThe string that matches the position of the index value. NULL if no string matches the position of the index value.
Parameters
NameOptionalDescription
IndexfalseNumeric datatype. Enter a number based on the position of the value you want to match.
StringfalseAny character value.
Examples
Choose(1, "SentryOne, 4001 Yancey Road, Charlotte, NC") - Will return "SentryOne"
Choose(1, varPipeDelimetedAddress, "|")
Chr

Chr

DescriptionCHR 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.
SyntaxCHR( Numeric_Value )
ReturnsASCII or Unicode character. A string containing one character. NULL if a value passed to the function is NULL.
Parameters
NameOptionalDescription
Numeric_ValuefalseNumeric 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

ChrCode

DescriptionChrCode 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.
SyntaxChrCode ( String )
ReturnsASCII or Unicode character. A string containing one character. NULL if a value passed to the function is NULL.
Parameters
NameOptionalDescription
StringfalseCharacter 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

Concat

DescriptionConcatenates two strings.
SyntaxCONCAT( First_String, Second_String )
ReturnsString. 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
NameOptionalDescription
First_StringfalseAny datatype except Binary. The first part of the string you want to concatenate. You can enter any valid task editor.
Second_StringfalseAny datatype except Binary. The second part of the string you want to concatenate. You can enter any valid task editor.
Examples
Concat("Sentry", " ", "One"), Concat(FirstNameColumn, " ", LastNameColumn)
ConvertBase

ConvertBase

DescriptionConverts a integer base from one to another.
SyntaxConvertBase( Value, From_Base, To_Base )
ReturnsInteger.
Parameters
NameOptionalDescription
ValuefalseThe value to change the base of.
From_BasefalseThe existing base of the integer. Valid values are 2, 8, 10, 16
To_BasefalseThe new base of the integer. Valid values are 2, 8, 10, 16
Examples
ConvertBase("1234", 10, 2)
Cos

Cos

DescriptionReturns the cosine of a numeric value (expressed in radians).
SyntaxCos( Numeric_Value )
ReturnsDouble value. NULL if a value passed to the function is NULL.
Parameters
NameOptionalDescription
Numeric_ValuefalseNumeric 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

Cosh

DescriptionReturns the hyperbolic cosine of a numeric value (expressed in radians).
SyntaxCosh( Numeric_Value )
ReturnsDouble value. NULL if a value passed to the function is NULL.
Parameters
NameOptionalDescription
Numeric_ValuefalseNumeric 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 (*)

Count (*)

DescriptionAggregation that calculates the total number based on the grouping. Can only be used as part of the Advanced Aggregation component
SyntaxCount( Value [, Condition])
ReturnsInteger.
Parameters
NameOptionalDescription
ValuefalseThe value to add to the aggregation
ConditionfalseThe 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)

Count (column name)

DescriptionAggregation that calculates the total number based on the grouping. Can only be used as part of the Advanced Aggregation component
SyntaxCount( Column_Name [, Condition])
ReturnsInteger.
Parameters
NameOptionalDescription
Column_NamefalseThe column to retrieve the value of to add to the aggregation
ConditionfalseThe 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.
Examples
Count(CustomerID), Count(CustomerID, CustomerState="FL")