Task Factory Functions List S

Second

Second

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

SendMail

DescriptionAllows the user to send an SMTP Message.
SyntaxSendMail( Send_To_Emails, From_Email [, CC] [, Bcc] [, Subject] [, Body] [, Is_Body_HTML] [, Smtp_Server] [, Smtp_Port], [, Username] [, Password] [, Attachments] [, Priority])
Parameters
NameOptionalDescription
Send_To_EmailsfalseA string that represents the E-mail Address(es) the user wishes the E-mail to be sent to. Multiple E-mails can be specified by seperating them with a semicolon (;).
From_EmailfalseA string representing the E-mail Address the user wishes the E-mail to be sent from.
CCtrueOptional. A string representing any E-mail Address(es) the user wishes to carbon-copy. Multiple E-mails can be specified by seperating them with a semicolon (;).
BcctrueOptional. A string representing any E-mail Address(es) the user wishes to blind carbon-copy. Multiple E-mails can be specified by seperating them with a semicolon (;).
SubjecttrueOptional. A string representing the subject for the Email.
BodytrueOptional. A string representing the Body for the Email
Is_Body_HTMLtrueOptional. A boolean value representing whether the Body is formated in HTML.
Smtp_ServertrueOptional. A string value representing the name of the Server you wish to use to send the E-mail.
Smtp_PorttrueOptional. An integer value representing the Port required to send the E-mail through.
UsernametrueOptional. A string value representing the username of the credentials required by the Smtp_Server to send the E-mail.
PasswordtrueOptional. A string value representing the password for the Username required by the Smtp_Server to send the E-mail.
AttachmentstrueOptional. A string value representing any Attachement(s) to include on the E-mail. Multiple attachments can be specified by seperating them with a Vertical Bar (|).
PrioritytrueOptional. A string value representing the Priority level of the E-mail. The Priority can be "High", "Low", or "Normal".
Sequence

Sequence

DescriptionReturns a unique number throughout your dataflow. You can use up to 5 different sequences at a time by calling SEQUENCE, SEQUENCE_2, SEQUENCE_3, SEQUENCE_4 or SEQUENCE_5
SyntaxSequence(), Sequence_2, Sequence(Reference_Count, Start_Value, Increment, Max_Value, Cycle_Value)
ReturnsNumeric (Int64)
Parameters
NameOptionalDescription
Reference_CounttrueA variable that will store the value of the current sequence.
Start_ValuetrueInteger. The staring point of the sequence value.
IncrementtrueInteger. The value in which the sequence will be incremented.
Max_ValuetrueLong. The maximum value of the sequence before the value is reset or an error is thrown based on the cycle parameter
Cycle_ValuetrueBoolean. If cycle is set to true and the maximum value (max_value) is hit, the sequence number will be reset to the start_value parameter. If cycle is set to false, when the max_value is hit, an error will be thrown
Examples
Sequence(), Sequence_5, Sequence(varRefValue, 1, 1, 100000, false)
SetConnectionProp

SetConnectionProp

DescriptionSet a property on the connection manager.
SyntaxSetConnectionProp( Connection_Manager, Property_Name, Property_Value )
ReturnsString.
Parameters
NameOptionalDescription
Connection_ManagerfalseConnection manager to set a property on. Must use the @@[connection_manager_name] syntax.
Property_NamefalseString value. The property to set on the connection manager.
Property_ValuefalseString value. The property value to set on the connection manager.
Examples
SetConnectionProp(@@[localhost.AdventureWorks], "ServerName", "localhost\sql2008")
 SetDatePart

SetDatePart

DescriptionSet any part of supplied date/time to user defined value.
SyntaxSetDatePart( Date, Format, Value)
ReturnsDate.
Parameters
NameOptionalDescription
DatefalseThe value to add to the aggregation
FormatfalseDate part format specifier (See below). 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 />
Valuefalsenew value for specified date part.
Examples
SetDatePart(Now(),"D", 1)
 SetVariable

SetVariable

DescriptionSets the current value of a mapping variable to a value you specify. Returns the specified value. The SETVARIABLE function executes only if a row is marked as insert or update. SETVARIABLE ignores all other row types and the current value remains unchanged.
SyntaxSETVARIABLE(@[Namespace::VariableName], Value )
ReturnsCurrent value of the variable. When value is NULL, the Integration Service returns the current value of $$Variable.
Parameters
NameOptionalDescription
VariableNamefalseNamespace and Name of the mapping variable you want to set. Use with mapping variables with Max/Min aggregation type.
ValuefalseThe value you want to set the current value of the variable to. You can enter any valid task editor that evaluates to a datatype compatible with the datatype of the variable.
Examples
SetVariable(@[User::FirstNameVar], "Chris"), SetVariable(@[User::FirstNameVar], FirstNameColumn)
 Sign

Sign

DescriptionReturns the value indicating the sign of a number.
SyntaxSign( Numeric_Value )
ReturnsNumeric
Parameters
NameOptionalDescription
Numeric_ValuefalseThe value to return the sign of
Examples
Sign(55)
 Sin

Sin

DescriptionReturns the sine of the specified angle.
SyntaxSin( Numeric_Value )
ReturnsNumeric
Parameters
NameOptionalDescription
Numeric_ValuefalsePasses the values for which you want to the sine of
Examples
Sin(55)
Sinh

Sinh

DescriptionReturns the hyperbolic tangent of the specified angle.
SyntaxSinh( Numeric_Value )
ReturnsNumeric
Parameters
NameOptionalDescription
Numeric_ValuefalsePasses the values for which you want to the sine of
Examples
Sinh(55)
Sleep

Sleep

DescriptionPause the processing of a package for x milliseconds.
SyntaxSleep( Milliseconds_To_Sleep )
ReturnsNULL
Parameters
NameOptionalDescription
Milliseconds_To_SleepfalseThe number of milliseconds to pause processing.
Examples
Sleep(6000), Sleep(varNumberOfSecondsToSleep)
 SoundEx

SoundEx

DescriptionEncodes a string value into a four-character string. SOUNDEX works for characters in the English alphabet (A-Z). It uses the first character of the input string as the first character in the return value and encodes the remaining three unique consonants as numbers.
SyntaxSOUNDEX( String )
ReturnsString. NULL if one of the following conditions is true: ¨ If value passed to the function is NULL. ¨ No character in string is a letter of the English alphabet. ¨ string is empty.
Parameters
NameOptionalDescription
StringfalseCharacter string. Passes the string value you want to encode. You can enter any valid transformation expression.
Examples
SoundEx("Sentr"), SoundEx(varSoundEx)
Space

Space

DescriptionReturns a string containing a specified number of spaces.
SyntaxSpace( Number_Of_Spaces )
ReturnsString. NULL if a value passed to the function is NULL.
Parameters
NameOptionalDescription
Number_Of_SpacesfalseThe number of spaces to create.
Examples
Space(10), Space(varNumberOfSpaces)
Sqrt

Sqrt

DescriptionReturns the square root of a specified number.
SyntaxSqrt( Numeric_Value )
ReturnsNumeric
Parameters
NameOptionalDescription
Numeric_ValuefalsePasses the values for which you want to the square root of
Examples
Sqrt(55)
StartsWith

StartsWith

DescriptionStartsWith determines whether a string starts with a character or string value
SyntaxStartsWith ( String_To_Search, Search_Value )
ReturnsBoolean. True if string_to_search ends with search_value. NULL if a value passed to the function is NULL.
Parameters
NameOptionalDescription
stringfalseCharacter string. The string to search.
stringfalseCharacter string. The value to find at the start of String_To_Search
Examples
StartsWith("400 College Dr", "Dr"), If StartsWith(AddressColumn, "400") Then ...)
 SubStr

SubStr

DescriptionReturns a portion of a string. SUBSTR counts all characters, including blanks, starting at the beginning of the string.
SyntaxSubStr( String, Start [, Length] )
ReturnsString. Empty string if you pass a negative or 0 length value. NULL if a value passed to the function is NULL.
Parameters
NameOptionalDescription
StringfalseMust be a character string. Passes the string you want to extract a string from. You can enter any valid task editor. If you pass a numeric value, the function converts it to a character string.
StartfalseMust be an integer. The position in the string where you want to start counting. You can enter any valid task editor. If the start position is a positive number, SUBSTR locates the start position by counting from the beginning of the string. If the start position is a negative number, SUBSTR locates the start position by counting from the end of the string. If the start position is 0, SUBSTR searches from the first character in the string.
LengthtrueMust be an integer greater than 0. The number of characters you want SUBSTR to return. You can enter any valid task editor. If you omit the length argument, SUBSTR returns all of the characters from the start position to the end of the string. If you pass a negative integer or 0, the function returns an empty string. If you pass a decimal, the function rounds it to the nearest integer value.
Examples
Substr("This will be cut off here. This won't be here", 0, 27)
Sum

Sum

DescriptionAggregation that calculates the total of all values. Can only be used as part of the Advanced Aggregation component
SyntaxSum( Value [, Condition])
ReturnsDouble.
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 Sum(SalesTotal, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida.
Examples
Sum(SalesTotal), Sum(SalesTotal, CustomerState="FL")
SysTimeStamp

SysTimeStamp

DescriptionReturns the current date and time of the node hosting the Integration Service with precision to the nanosecond
SyntaxSysTimeStamp( [Format] )
ReturnsDate.
Parameters
NameOptionalDescription
FormattrueOptional. Sets the precision of the System Time. If Format is not specified, it defaults to nanoseconds. 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 />