Task Factory Functions List K-L

Last

Last

DescriptionAggregation that returns the last value of the group. Can only be used as part of the Advanced Aggregation component
SyntaxLast( Value [, Condition])
ReturnsObject.
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 Last(SalesTotal, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida.
Examples
Last(SalesTotal), Last(SalesTotal, CustomerState="FL")
LastDate

LastDate

DescriptionReturns the last day of a specified month.
SyntaxLastDate(), LastDate( Date )
Returnsreturns date
Parameters
NameOptionalDescription
DatefalseDate to retrieve value from
Examples
LastDate(), LastDate("12/8/2011"), LastDate(varDate), LastDate(OrderDateColumn)
LastDateOfPrevMonth

LastDateOfPrevMonth

DescriptionReturns the last day of the previous month.
SyntaxLastDateOfPrevMonth(), LastDateOfPrevMonth( Date )
Returnsreturns date
Parameters
NameOptionalDescription
DatefalseDate to retrieve value from
Examples
LastDateOfPrevMonth(), LastDateOfPrevMonth("12/8/2011"), LastDateOfPrevMonth(varDate), LastDateOfPrevMonth(OrderDateColumn)
Least

Least

DescriptionReturns the smallest value from a list of input values. By default, the match is not case sensitive.
SyntaxLEAST( Case_Flag, Value1 [, Value2, ..., ValueN,] )
Returnsvalue1 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
NameOptionalDescription
CaseFlagfalseDetermines 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.
ValuefalseAny 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.
Examples
Least(false, varValues), Least(false, "test1", "test2", "test3")
 Left

Left

DescriptionReturns a string containing a specified number of characters from the left side of a string..
SyntaxLeft( String, Number_Of_Characters )
ReturnsString. NULL if a value passed to the function is NULL.
Parameters
NameOptionalDescription
StringfalseString datatype. String expression from which the leftmost characters are returned.
Number_Of_CharactersfalseThe number of characters to return from the string.
Examples
Left("Stop Here. None of this will be there", 10), Left(FirstNameColumn, 20)
Length

Length

DescriptionReturns the number of characters in a string, including trailing blanks.
SyntaxLength( String )
ReturnsInteger representing the length of the string. NULL if a value passed to the function is NULL.
Parameters
NameOptionalDescription
StringfalseString datatype. The strings you want to evaluate. You can enter any valid transformation expression.
Examples
Length("How Long Is This?"), Length(FirstNameColumn)
Log

Log

DescriptionReturns the logarithm of a numeric value. Most often, you use this function to analyze scientific data rather than business data.
SyntaxLOG( Base, Exponent )
ReturnsDouble value. NULL if a value passed to the function is NULL.
Parameters
NameOptionalDescription
BasefalseThe 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.
ExponentfalseThe 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

Log10

DescriptionReturns the base 10 logarithm of a specified number.
SyntaxLog10( Numeric_Value )
ReturnsNumeric
Parameters
NameOptionalDescription
Numeric_ValuefalsePasses the values for which you want to the Atan of
Examples
Log10(56)
 LogBufferRowToFile

LogBufferRowToFile

DescriptionLogs a row of data from a dataflow to a flat file.
SyntaxLogBufferRowToFile(File_Path [, Delimeter] [, Quoted_String])
ReturnsNothing
Parameters
NameOptionalDescription
File_PathfalseThe path to the file to log the buffer row to.
DelimeteroptionalThe file delimeter to be used to seperate the column values. Default is ","
Quoted_StringoptionalTells the engine whether to wrap string values in quotes or not. Default is false.
Examples
LogBufferRowToFile("C:\temp\logfile.ext"), LogBufferRowToFile("c:\temp\logfile.txt", "|", True)]
LogError

LogError

DescriptionLogs an error message to the ssis output log (progress tab in BIDS)
SyntaxLogError(Message [, Condition])
ReturnsNothing
Parameters
NameOptionalDescription
MessagefalseThe message to log.
ConditiontrueThe condition to evaluate to true before logging the message
Examples
LogError("This is a error"), LogError("Values didn't match", value1!=value2)]
LogInfo

LogInfo

DescriptionLogs an informational message to the ssis output log (progress tab in BIDS)
SyntaxLogInfo(Message [, Condition])
ReturnsNothing
Parameters
NameOptionalDescription
MessagefalseThe message to log.
ConditiontrueThe condition to evaluate to true before logging the message
Examples
LogInfo("This is a message"), LogInfo("Values didn't match", value1!=value2)]
LogWarning

LogWarning

DescriptionLogs a warning message to the ssis output log (progress tab in BIDS)
SyntaxLogWarning(Message [, Condition])
ReturnsNothing
Parameters
NameOptionalDescription
MessagefalseThe message to log.
ConditiontrueThe condition to evaluate to true before logging the message
Examples
LogWarning("This is a warning"), LogWarning("Values didn't match", value1!=value2)]
LookupData

LookupData

DescriptionLookupData 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.
SyntaxLookupData(Cache_Connection_Manager, Arg1 [, Arg2, ..., ArgN])
Returnsreturns the value of the output column if a match is found. Returns NULL if no match is found.
Parameters
NameOptionalDescription
Cache_Connection_ManagerfalseThe Advanced Lookup Cache Connection Manager where the lookup will be performanced. Must use the @@[ConnectionManager] syntax.
ArgsfalseArgs 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

LookupDataByColumn

DescriptionLookupDataByColumn 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.
SyntaxLookupDataByColumn(Cache_Connection_Manager, Column_To_Retrieve, Arg1 [, Arg2, ..., ArgN])
Returnsreturns the value of the output column if a match is found. Returns NULL if no match is found.
Parameters
NameOptionalDescription
Cache_Connection_ManagerfalseThe Advanced Lookup Cache Connection Manager where the lookup will be performanced. Must use the @@[ConnectionManager] syntax.
Column_To_RetrievefalseThe name of an column defined as "Output" on the Advanced Lookup Cache Connection Manager.
ArgsfalseArgs 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

Lower

DescriptionConverts uppercase string characters to lowercase.
SyntaxLOWER( String )
ReturnsLowercase 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
NameOptionalDescription
StringfalseAny 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

LPad

DescriptionAdds a set of blanks or characters to the beginning of a string to set the string to a specified length.
SyntaxLPad( First_String, Length [, Second_String] )
ReturnsString of the specified length. NULL if a value passed to the function is NULL or if length is a negative number.
Parameters
NameOptionalDescription
First_StringfalseCan be a character string. The strings you want to change. You can enter any valid task editor.
LengthfalseMust be a positive integer literal. This argument specifies the length you want each string to be.
Second_StringtrueCan 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.
Examples
LPad("SentryOne", 30), LPad(CompanyNameColumn, 50)
 LTrim

LTrim

DescriptionRemoves blanks or characters from the beginning of a string.
SyntaxLTRIM( String [, Trim_Set] )
ReturnsString. 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
NameOptionalDescription
StringfalseAny 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_SettrueAny 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'.
Examples
LTrim(" SentryOne"), LTrim(CompanyNameColumn), LTrim("----SentryOne", "-")