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) |