Task Factory Functions List M-N

MakeDateTime

MakeDateTime

DescriptionCreates a date based on the parameters passed into the function.
SyntaxMakeDateTime(Year, Month, Day [, Hour] [, Minute] [, Second] [, Millisecond])
Returnsreturns date
Parameters
NameOptionalDescription
YearfalseYear of the date being created
MonthfalseMonth of the date being created
DayfalseDay of the date being created
HourfalseHour of the date being created
MinutefalseMinute of the date being created
SecondfalseSecond of the date being created
MillisecondfalseMillisecond of the date being created
Examples
MakeDateTime("2012", "07", "07"), MakeDateTime("2012", "07", "07", "12", "30", "45", "001")
 Max(Date)

Max (Date)

DescriptionAggregation that returns max date the group. Can only be used as part of the Advanced Aggregation component
SyntaxMax( Value [, Condition])
ReturnsDate.
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 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)

Max (Number)

DescriptionAggregation that returns max number the group. Can only be used as part of the Advanced Aggregation component
SyntaxMax( Value [, Condition])
ReturnsInteger.
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 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)

Max (String)

DescriptionAggregation 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
SyntaxMax( Value [, Condition])
ReturnsString
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 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

MD5

DescriptionCalculates 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.
SyntaxMD5( Value )
ReturnsUnique 32-character string of hexadecimal digits 0-9 and a-f. NULL if the input is a null value.
Parameters
NameOptionalDescription
ValuefalseString 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

MessageBox

DescriptionDisplays a MessageBox with a message (Only works in BIDS / SQL Server Data Tools). Used for debugging purposes during design time.
SyntaxMessageBox(Message [, Condition])
ReturnsNothing
Parameters
NameOptionalDescription
MessagefalseThe message to display.
ConditiontrueThe condition to evaluate to true before display the MessageBox
Examples
MessageBox("This is a message"), MessageBox("Values didn't match", value1!=value2)]
MilliSecond

MilliSecond

DescriptionReturns the MilliSecond from the date specified.
SyntaxMilliSecond( Date )
Returnsreturns integer value of 0 - 999
Parameters
NameOptionalDescription
DatefalseDate to retrieve MilliSecond from
Examples
MilliSecond("12/8/2011"), MilliSecond(varDate), MilliSecond(OrderDateColumn)
 Min (Date)

Min (Date)

DescriptionAggregation that returns Min date the group. Can only be used as part of the Advanced Aggregation component
SyntaxMin( Value [, Condition])
ReturnsDate.
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 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)

Min (Number)

DescriptionAggregation that returns Min number the group. Can only be used as part of the Advanced Aggregation component
SyntaxMin( Value [, Condition])
ReturnsInteger.
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 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)

Min (String)

DescriptionAggregation 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
SyntaxMin( Value [, Condition])
ReturnsString
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 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

Minute

DescriptionReturns the Minute from the date specified.
SyntaxMinute( Date )
Returnsreturns integer value of 0 - 59
Parameters
NameOptionalDescription
DatefalseDate to retrieve Minute from
Examples
Minute("12/8/2011"), Minute(varDate), Minute(OrderDateColumn)
 Mod

Mod

DescriptionDivides two numbers and returns the remainder.
SyntaxMod( Value1, Value2 )
ReturnsNumeric
Parameters
NameOptionalDescription
Value1falseThe first number in the equation.
Value2falseThe second number in the equation.
Examples
Mod(10, 3)
Month

Month

DescriptionReturns the Month from the date specified.
SyntaxMonth( Date )
Returnsreturns integer value of 1 - 12
Parameters
NameOptionalDescription
DatefalseDate to retrieve Month from
Examples
Month("12/8/2011"), Month(varDate), Month(OrderDateColumn)
MonthName

MonthName

DescriptionReturns the name of the Month (MonMonth, TuesMonth, etc) from the date specified.
SyntaxMonthName( Date )
Returnsreturns string. Name of the Month
Parameters
NameOptionalDescription
DatefalseDate to retrieve Month name from
return_shortnamefalseReturn the short name of the Month (Jan, Feb, Mar)
Examples
MonthName("12/8/2011", false), MonthName(varDate, True), MonthName(OrderDateColumn, false)
Now

Now

DescriptionReturns the current date and time.
SyntaxNow()
ReturnsDate
Examples
Now()
 NullIf

NullIf

DescriptionCompares two string values for equality and returns null if they match.
SyntaxNullIf( String_Value1, String_Value2 )
ReturnsNull if "String_Value1" or "String_Value2" is null. Returns value of "String_Value1" if "String_Value2" and "string_value2" do not match..
Parameters
NameOptionalDescription
String_Value1falseAny string value. If the values do not match, this value is returned from the function.
String_Value2falseAny string value.
Examples
NullIf(CompanyNameColumn, ""), NullIf(AddressColumnLine2, "")
NullIfEmpty

NullIfEmpty

DescriptionChecks a string value for an empty value. If the string value is empty a null value is returned.
SyntaxNullIfEmpty( String_Value )
ReturnsNull if "String_Value" is empty. Returns value of "String_Value" if the value is not empty.
Parameters
NameOptionalDescription
String_ValuefalseAny string value.
Examples
NullIfEmpty(CompanyNameColumn), NullIfEmpty(varValue)