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.
Last
Description
Aggregation that returns the last value of the group. Can only be used as part of the Advanced Aggregation component
Syntax
Last( Value [, Condition])
Returns
Object.
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 Last(SalesTotal, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida.
value1 if it is the smallest of the input values, value2 if it is the smallest of the input values, and so on. NULL if any of the arguments is null.
Parameters
Name
Optional
Description
CaseFlag
false
Determines whether the arguments in this function are case sensitive. You can enter any valid transformation expression. When CaseFlag is a number other than 0, the function is case sensitive. When CaseFlag is a null value or 0, the function is not case sensitive.
Value
false
Any datatype except Binary. Datatype must be compatible with other values. Value you want to compare against other values. You must enter at least one value argument. If the value is Numeric, and other input values are of other numeric datatypes, all values use the highest precision possible. For example, if some values are of the Integer datatype and others are of the Double datatype, the Integration Service converts the values to Double.
Returns a string containing a specified number of characters from the left side of a string..
Syntax
Left( String, Number_Of_Characters )
Returns
String. NULL if a value passed to the function is NULL.
Parameters
Name
Optional
Description
String
false
String datatype. String expression from which the leftmost characters are returned.
Number_Of_Characters
false
The number of characters to return from the string.
Examples
Left("Stop Here. None of this will be there", 10), Left(FirstNameColumn, 20)
Length
Description
Returns the number of characters in a string, including trailing blanks.
Syntax
Length( String )
Returns
Integer representing the length of the string. NULL if a value passed to the function is NULL.
Parameters
Name
Optional
Description
String
false
String datatype. The strings you want to evaluate. You can enter any valid transformation expression.
Examples
Length("How Long Is This?"), Length(FirstNameColumn)
Log
Description
Returns the logarithm of a numeric value. Most often, you use this function to analyze scientific data rather than business data.
Syntax
LOG( Base, Exponent )
Returns
Double value. NULL if a value passed to the function is NULL.
Parameters
Name
Optional
Description
Base
false
The base of the logarithm. Must be a positive numeric value other than 0 or 1. Any valid task editor that evaluates to a positive number other than 0 or 1.
Exponent
false
The exponent of the logarithm. Must be a positive numeric value greater than 0. Any valid task editor that evaluates to a positive number greater than 0.
Examples
Log( BaseColumn, ExponentColumn)
Log10
Description
Returns the base 10 logarithm of a specified number.
Syntax
Log10( Numeric_Value )
Returns
Numeric
Parameters
Name
Optional
Description
Numeric_Value
false
Passes the values for which you want to the Atan of
Examples
Log10(56)
LogBufferRowToFile
Description
Logs a row of data from a dataflow to a flat file.
Logs an error message to the ssis output log (progress tab in BIDS)
Syntax
LogError(Message [, Condition])
Returns
Nothing
Parameters
Name
Optional
Description
Message
false
The message to log.
Condition
true
The condition to evaluate to true before logging the message
Examples
LogError("This is a error"), LogError("Values didn't match", value1!=value2)]
LogInfo
Description
Logs an informational message to the ssis output log (progress tab in BIDS)
Syntax
LogInfo(Message [, Condition])
Returns
Nothing
Parameters
Name
Optional
Description
Message
false
The message to log.
Condition
true
The condition to evaluate to true before logging the message
Examples
LogInfo("This is a message"), LogInfo("Values didn't match", value1!=value2)]
LogWarning
Description
Logs a warning message to the ssis output log (progress tab in BIDS)
Syntax
LogWarning(Message [, Condition])
Returns
Nothing
Parameters
Name
Optional
Description
Message
false
The message to log.
Condition
true
The condition to evaluate to true before logging the message
Examples
LogWarning("This is a warning"), LogWarning("Values didn't match", value1!=value2)]
LookupData
Description
LookupData will use an Advanced Lookup Cache Connection Manager to retrieve data from the cached connection manager. LookupData will return the value of the first output column defined on a cache connection manager. The args parameter array is used to pass in values that match the "Input Parameters" defined on a cache connection manager.
returns the value of the output column if a match is found. Returns NULL if no match is found.
Parameters
Name
Optional
Description
Cache_Connection_Manager
false
The Advanced Lookup Cache Connection Manager where the lookup will be performanced. Must use the @@[ConnectionManager] syntax.
Args
false
Args is a parameter array of values that directly correlate to the input parameters defined on a cache connection manager.
Examples
LookupData(@@[ProductCache], ProductID)] - In this example, the cache connection manager (@@[ProductCache]) is storing the products. ProductID is a column that is part of the current component being used (Advanced Derived Column, Advanced Aggregator, etc) and the value of ProductID is passed into LookupData from the Data Flow. [LookupData(@@[ProductCache], ProductID, OrderDate)] - In this example, the cache connection manager (@@[ProductCache]) is storing the products. ProductID and OrderDate are columns that are part of the current component being used (Advanced Derived Column, Advanced Aggregator, etc) and the value of them is passed into LookupData from the Data Flow.
LookupDataByColumn
Description
LookupDataByColumn will use an Advanced Lookup Cache Connection Manager to retrieve data from the cached connection manager and return a specified. Unlike LookupData, which returns the value of the first output column defined on a cache connection manager, LookupDataByColumn can be used to grab a specific column. The args parameter array is used to pass in values that match the "Input Parameters" defined on a cache connection manager.
returns the value of the output column if a match is found. Returns NULL if no match is found.
Parameters
Name
Optional
Description
Cache_Connection_Manager
false
The Advanced Lookup Cache Connection Manager where the lookup will be performanced. Must use the @@[ConnectionManager] syntax.
Column_To_Retrieve
false
The name of an column defined as "Output" on the Advanced Lookup Cache Connection Manager.
Args
false
Args is a parameter array of values that directly correlate to the input parameters defined on a cache connection manager.
Examples
LookupData(@@[ProductCache], "ProductName", ProductID)] - In this example, the cache connection manager (@@[ProductCache]) is storing the products. ProductID is a column that is part of the current component being used (Advanced Derived Column, Advanced Aggregator, etc) and the value of ProductID is passed into LookupData from the Data Flow. "ProductName" is the column being retrieved from the cache connection manager. [LookupData(@@[ProductCache], "ProductCost", ProductID, OrderDate)] - In this example, the cache connection manager (@@[ProductCache]) is storing the products. ProductID and OrderDate are columns that are part of the current component being used (Advanced Derived Column, Advanced Aggregator, etc) and the value of them is passed into LookupData from the Data Flow. "ProductCost" is the column being retrieved from the cache connection manager.
Lower
Description
Converts uppercase string characters to lowercase.
Syntax
LOWER( String )
Returns
Lowercase character string. If the data contains multibyte characters, the return value depends on the code page and data movement mode of the Integration Service. NULL if a value in the selected port is NULL.
Parameters
Name
Optional
Description
String
false
Any string value. The argument passes the string values that you want to return as lowercase. You can enter any valid transformation expression that evaluates to a string.
Examples
Lower("Lower All THIS"), Lower(LastNameColumn)
LPad
Description
Adds a set of blanks or characters to the beginning of a string to set the string to a specified length.
Syntax
LPad( First_String, Length [, Second_String] )
Returns
String of the specified length. NULL if a value passed to the function is NULL or if length is a negative number.
Parameters
Name
Optional
Description
First_String
false
Can be a character string. The strings you want to change. You can enter any valid task editor.
Length
false
Must be a positive integer literal. This argument specifies the length you want each string to be.
Second_String
true
Can be any string value. The characters you want to append to the left-side of the first_string values. You can enter any valid task editor. You can enter a specific string literal. However, enclose the characters you want to add to the beginning of the string within single quotation marks, as in 'abc'. This argument is case sensitive. If you omit the second_string, the function pads the beginning of the first string with blanks.
Removes blanks or characters from the beginning of a string.
Syntax
LTRIM( String [, Trim_Set] )
Returns
String. The string values with the specified characters in the trim_set argument removed. NULL if a value passed to the function is NULL. If the trim_set is NULL, the function returns NULL.
Parameters
Name
Optional
Description
String
false
Any string value. Passes the strings you want to modify. You can enter any valid task editor. Use operators to perform comparisons or concatenate strings before removing characters from the beginning of a string.
Trim_Set
true
Any string value. Passes the characters you want to remove from the beginning of the first string. You can enter any valid task editor. You can also enter a character string. However, you must enclose the characters you want to remove from the beginning of the string within single quotation marks, for example, 'abc'. If you omit the second string, the function removes any blanks from the beginning of the string. LTRIM is case sensitive. For example, if you want to remove the 'A' character from the string 'Alfredo', you would enter 'A', not 'a'.
