Task Factory Functions List T-Z
Tan |
||
|---|---|---|
| Description | Returns the tangent of the specified angle. | |
| Syntax | Tan( Numeric_Value ) | |
| Returns | Numeric | |
| Parameters | ||
| Name | Optional | Description |
| Numeric_Value | false | Passes the values for which you want to the tangent of |
| Examples | ||
| Tan(55) | ||
Tanh |
||
|---|---|---|
| Description | Returns the hyperbolic tangent of the specified angle. | |
| Syntax | Tanh( Numeric_Value ) | |
| Returns | Numeric | |
| Parameters | ||
| Name | Optional | Description |
| Numeric_Value | false | Passes the values for which you want to the tangent of |
| Examples | ||
| Tanh(55) | ||
ToBigint |
||
|---|---|---|
| Description | Converts a value to a big int (Int64) | |
| Syntax | ToBigint( Value ) | |
| Returns | Int64 value | |
| Parameters | ||
| Name | Optional | Description |
| Value | false | The value to convert. |
| Examples | ||
| ToBigint(32), To_Big_Int(89090989) | ||
ToChar |
||
|---|---|---|
| Description | Converts a value to a string with optional formatting for date values | |
| Syntax | ToChar( Value [, Format] ) | |
| Returns | String value | |
| Parameters | ||
| Name | Optional | Description |
| Value | false | The value to convert. |
| Format | false | The formatting that will applied when the date value is converted. This is the equivalent of calling DateTime.ToString(). |
| Examples | ||
| ToChar(32000), ToChar(Now(), "yyyy/MM/dd") | ||
ToDate |
||
|---|---|---|
| Description | Converts a string value to a date with optional formatting | |
| Syntax | ToDate( Value [, Format] ) | |
| Returns | String value | |
| Parameters | ||
| Name | Optional | Description |
| Value | false | The value to convert. |
| Format | false | The formatting that will applied when the date value is converted. This is the equivalent of calling DateTime.ToString(). |
| Examples | ||
| ToDate("07/07/2012"), ToDate(Now(), "yyyy/MM/dd") | ||
Today |
||
|---|---|---|
| Description | Returns the current date without the time. | |
| Syntax | Today() | |
| Returns | Date | |
| Examples | ||
| Today() | ||
ToDecimal |
||
|---|---|---|
| Description | Converts a value to a decimal value. | |
| Syntax | ToDecimal( Value [, Scale] ) | |
| Returns | Decimal | |
| Parameters | ||
| Name | Optional | Description |
| Value | false | The value to convert. |
| Scale | false | The scale used to convert the decimal value. |
| Examples | ||
| ToDecimal("1234.78"), ToDecimal("45876.765", 2) | ||
ToFloat |
||
|---|---|---|
| Description | Converts a value to a float value. | |
| Syntax | ToFloat( Value ) | |
| Returns | Float | |
| Parameters | ||
| Name | Optional | Description |
| Value | false | The value to convert. |
| Examples | ||
| ToFloat("1234.78") | ||
ToInteger |
||
|---|---|---|
| Description | Converts a value to an integer value. | |
| Syntax | ToInteger( Value ) | |
| Returns | Integer. Any decimal values will be rounded. | |
| Parameters | ||
| Name | Optional | Description |
| Value | false | The value to convert. |
| Examples | ||
| ToInteger("1234.78") | ||
Token |
||
|---|---|---|
| Description | Splits a string based on a delimeter. | |
| Syntax | Token( Input_String, Delimeter, occurrence ) | |
| Returns | String. If the input_string or delimeter is NULL, the function returns NULL. | |
| Parameters | ||
| Name | Optional | Description |
| Input_String | false | A string value that will be searched. |
| Delimeter | false | A delimeter to be used to split the value of Input_String. |
| Occurrence | false | The occurrence that will be returned after the string has been split. |
| Examples | ||
| Token("400 College Dr|Middleburg|FL"|", 2) returns "Middleburg" | ||
TokenCount |
||
|---|---|---|
| Description | Gets the number of times a token occurs in a string. | |
| Syntax | TokenCount( Input_String, Token ) | |
| Returns | Integer. If the input_string or token is NULL, the function returns NULL. | |
| Parameters | ||
| Name | Optional | Description |
| Input_String | false | A string value that will be searched by the token value. |
| Token | false | A string expression to be located in Input_String. |
| Examples | ||
| TokenCount("Sentry~44~One~55~Inc", "~") | ||
Trim |
||
|---|---|---|
| Description | Removes blanks or characters from the beginning and end of a string. | |
| Syntax | Trim( 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 and end of the string. Trim is case sensitive. For example, if you want to remove the 'A' character from the string 'Alfredo', you would enter 'A', not 'a'. |
| Examples | ||
| Trim(" SentryOne "), Trim(CompanyNameColumn), Trim("----SentryOne", "-") | ||
Trunc(Date) |
||
|---|---|---|
| Description | Truncates date to a specific year, month, day, hour, minute, second, millisecond, or microsecond. | |
| Syntax | Trunc( Date [, Format]) | |
| Returns | Date. | |
| Parameters | ||
| Name | Optional | Description |
| Date | false | The value to add to the aggregation |
| Format | true | Anything after this part will be reset to default value. e.g. if you pass HH then anything after Hour (e.g. Minute, Second, Milliseconds) will be reset to 0. Valid format specifiers Year = "Y", "YY", "YYY", "YYYY"<br /> Day = "D", "DD", "DDD", "DY", "DAY"<br /> Month = "M", "MM", "MON", "MONTH"<br /> Hour = "H", "HH", "HH12", "HH24"<br /> Minute = "MI"<br /> Second = "S", "SS""<br /> Millisecond = "MS"<br /> Microsecond = "US" |
| value | false | new value for specified date part. |
| Examples | ||
| SetDatePart(Now(),"D", 1) | ||
Trunc(Number) |
||
|---|---|---|
| Description | Truncates specified decimal places from numeric value. | |
| Syntax | Trunc( Number [, Precision]) | |
| Returns | Number. | |
| Parameters | ||
| Name | Optional | Description |
| Number | false | The number you want to truncate |
| Precision | true | Number of decimal places to keep. If you pass 0 then all digits after decimal point will be truncated. If you pass -ve precision number then it will truncate digits before decimal point. |
| Examples | ||
| Trunc(567.7789,2) --> 567.7700 Trunc(567.7789) --> 567.0000 Trunc(567.7789,-2) --> 500.0000 | ||
TryEval |
||
|---|---|---|
| Description | Returns defaultvalue if supplied expression fails to execute. This function help to continue execution on error and it behaves like Try..Catch construct provided in other programming languages such as VB.net/C#. | |
| Syntax | TryEval( expression, defaultvalue [, logerrors] ) | |
| Returns | Returns result of main expression if succueed else returns defaultvalue. | |
| Parameters | ||
| Name | Optional | Description |
| expression | false | Any valid expression (without compiler error). E.g. MyCol/0 is valid expression but produce "Divide By Zero" error at runtime. |
| defaultvalue | false | Any valid expression or value which can be returned when main expression fails. |
| logerrors | true | Boolean value indicating whether error message should be logged or not. If this argument is True (which is default setting) then error messages will appear in you ssis package execution log as Information icon [e.g. <<EVAL-ERROR at="" ROW=""# 234="">> some error occurred]. |
| Examples | ||
| TryEval(TO_CHAR("1")/TO_CHAR("0") , -999 ) -- Returns -999 becuase TO_CHAR("1")/TO_CHAR("0") fails with DivideByZero error TryEval(TO_DATE("55/12/1231","MM/DD/YYYY"), GETDATE() ) -- Returns today's date if date parsing fails | ||
Upper |
||
|---|---|---|
| Description | Converts lowercase string characters to uppercase. | |
| Syntax | UPPER( String ) | |
| Returns | Uppercase 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 passed to the function is NULL. | |
| Parameters | ||
| Name | Optional | Description |
| String | false | String datatype. Passes the values you want to change to uppercase text. You can enter any valid task editor. |
| Examples | ||
| Upper("this will be uppercase now"), IIF(Upper(FirstNameColumn) = "CHRIS", 1, 0) | ||
WeekOfYear |
||
|---|---|---|
| Description | Returns the week number of the year of the date specified. | |
| Syntax | WeekOfYear( Date ) | |
| Returns | returns integer value of 1 - 52 | |
| Parameters | ||
| Name | Optional | Description |
| Date | false | Date to retrieve week of year from |
| Examples | ||
| WeekOfYear("12/8/2011"), WeekOfYear(varDate), WeekOfYear(OrderDateColumn) | ||
Year |
||
|---|---|---|
| Description | Returns the Year from the date specified. | |
| Syntax | Year( Date ) | |
| Returns | returns integer value of year | |
| Parameters | ||
| Name | Optional | Description |
| Date | false | Date to retrieve Year from |
| Examples | ||
| Year("12/8/2011"),Year(varDate), Year(OrderDateColumn) | ||