Task Factory Functions List M-N
Last Modified on 02 October 2020
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.
MakeDateTime |
|---|
| Description | Creates a date based on the parameters passed into the function. |
| Syntax | MakeDateTime(Year, Month, Day [, Hour] [, Minute] [, Second] [, Millisecond]) |
| Returns | returns date |
| Parameters |
| Name | Optional | Description |
| Year | false | Year of the date being created |
| Month | false | Month of the date being created |
| Day | false | Day of the date being created |
| Hour | false | Hour of the date being created |
| Minute | false | Minute of the date being created |
| Second | false | Second of the date being created |
| Millisecond | false | Millisecond of the date being created |
| Examples |
| MakeDateTime("2012", "07", "07"), MakeDateTime("2012", "07", "07", "12", "30", "45", "001") |
Max (Date) |
|---|
| Description | Aggregation that returns max date the group. Can only be used as part of the Advanced Aggregation component |
| Syntax | Max( Value [, Condition]) |
| Returns | Date. |
| 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 Max(SalesDate, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida. |
| Examples |
| Max(SalesDate), Max(SalesDate, CustomerState="FL") |
Max (Number) |
|---|
| Description | Aggregation that returns max number the group. Can only be used as part of the Advanced Aggregation component |
| Syntax | Max( 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 Max(SalesTotal, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida. |
| Examples |
| Max(SalesTotal), Max(SalesTotal, CustomerState="FL") |
Max (String) |
|---|
| Description | Aggregation that returns max string the group. The value is based on the ascii value of the string. Can only be used as part of the Advanced Aggregation component |
| Syntax | Max( Value [, Condition]) |
| Returns | String |
| 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 Max(CustomerAlternateKey, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida. |
| Examples |
| Max(CustomerAlternateKey), Max(CustomerAlternateKey, CustomerState="FL") |
MD5 |
|---|
| Description | Calculates the checksum of the input value. The function uses Message-Digest algorithm 5 (MD5). MD5 is a one- way cryptographic hash function with a 128-bit hash value. You can conclude that input values are different when the checksums of the input values are different. Use MD5 to verify data integrity. |
| Syntax | MD5( Value ) |
| Returns | Unique 32-character string of hexadecimal digits 0-9 and a-f. NULL if the input is a null value. |
| Parameters |
| Name | Optional | Description |
| Value | false | String or Binary datatype. Value for which you want to calculate checksum. The case of the input value affects the return value. For example, MD5(ssis) and MD5(SSIS) return different values. |
| Examples |
| MD5("secure this") |
MessageBox |
|---|
| Description | Displays a MessageBox with a message (Only works in BIDS / SQL Server Data Tools). Used for debugging purposes during design time. |
| Syntax | MessageBox(Message [, Condition]) |
| Returns | Nothing |
| Parameters |
| Name | Optional | Description |
| Message | false | The message to display. |
| Condition | true | The condition to evaluate to true before display the MessageBox |
| Examples |
| MessageBox("This is a message"), MessageBox("Values didn't match", value1!=value2)] |
MilliSecond |
|---|
| Description | Returns the MilliSecond from the date specified. |
| Syntax | MilliSecond( Date ) |
| Returns | returns integer value of 0 - 999 |
| Parameters |
| Name | Optional | Description |
| Date | false | Date to retrieve MilliSecond from |
| Examples |
| MilliSecond("12/8/2011"), MilliSecond(varDate), MilliSecond(OrderDateColumn) |
Min (Date) |
|---|
| Description | Aggregation that returns Min date the group. Can only be used as part of the Advanced Aggregation component |
| Syntax | Min( Value [, Condition]) |
| Returns | Date. |
| 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 Min(SalesDate, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida. |
| Examples |
| Min(SalesDate), Min(SalesDate, CustomerState="FL") |
Min (Number) |
|---|
| Description | Aggregation that returns Min number the group. Can only be used as part of the Advanced Aggregation component |
| Syntax | Min( 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 Min(SalesTotal, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida. |
| Examples |
| Min(SalesTotal), Min(SalesTotal, CustomerState="FL") |
Min (String) |
|---|
| Description | Aggregation that returns Min string the group. The value is based on the ascii value of the string. Can only be used as part of the Advanced Aggregation component |
| Syntax | Min( Value [, Condition]) |
| Returns | String |
| 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 Min(CustomerAlternateKey, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida. |
| Examples |
| Min(CustomerAlternateKey), Min(CustomerAlternateKey, CustomerState="FL") |
Minute |
|---|
| Description | Returns the Minute from the date specified. |
| Syntax | Minute( Date ) |
| Returns | returns integer value of 0 - 59 |
| Parameters |
| Name | Optional | Description |
| Date | false | Date to retrieve Minute from |
| Examples |
| Minute("12/8/2011"), Minute(varDate), Minute(OrderDateColumn) |
Mod |
|---|
| Description | Divides two numbers and returns the remainder. |
| Syntax | Mod( Value1, Value2 ) |
| Returns | Numeric |
| Parameters |
| Name | Optional | Description |
| Value1 | false | The first number in the equation. |
| Value2 | false | The second number in the equation. |
| Examples |
| Mod(10, 3) |
Month |
|---|
| Description | Returns the Month from the date specified. |
| Syntax | Month( Date ) |
| Returns | returns integer value of 1 - 12 |
| Parameters |
| Name | Optional | Description |
| Date | false | Date to retrieve Month from |
| Examples |
| Month("12/8/2011"), Month(varDate), Month(OrderDateColumn) |
MonthName |
|---|
| Description | Returns the name of the Month (MonMonth, TuesMonth, etc) from the date specified. |
| Syntax | MonthName( Date ) |
| Returns | returns string. Name of the Month |
| Parameters |
| Name | Optional | Description |
| Date | false | Date to retrieve Month name from |
| return_shortname | false | Return the short name of the Month (Jan, Feb, Mar) |
| Examples |
| MonthName("12/8/2011", false), MonthName(varDate, True), MonthName(OrderDateColumn, false) |
Now |
|---|
| Description | Returns the current date and time. |
| Syntax | Now() |
| Returns | Date |
| Examples |
| Now() |
NullIf |
|---|
| Description | Compares two string values for equality and returns null if they match. |
| Syntax | NullIf( String_Value1, String_Value2 ) |
| Returns | Null if "String_Value1" or "String_Value2" is null. Returns value of "String_Value1" if "String_Value2" and "string_value2" do not match.. |
| Parameters |
| Name | Optional | Description |
| String_Value1 | false | Any string value. If the values do not match, this value is returned from the function. |
| String_Value2 | false | Any string value. |
| Examples |
| NullIf(CompanyNameColumn, ""), NullIf(AddressColumnLine2, "") |
NullIfEmpty |
|---|
| Description | Checks a string value for an empty value. If the string value is empty a null value is returned. |
| Syntax | NullIfEmpty( String_Value ) |
| Returns | Null if "String_Value" is empty. Returns value of "String_Value" if the value is not empty. |
| Parameters |
| Name | Optional | Description |
| String_Value | false | Any string value. |
| Examples |
| NullIfEmpty(CompanyNameColumn), NullIfEmpty(varValue) |
