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)

Was this article helpful?

Copyright © SQL Sentry, LLC. All rights reserved.