Documentation forTask Factory

Task Factory Functions List S

 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.

Second

Description Returns the Second from the date specified.
Syntax Second( Date )
Returns returns integer value of 0 - 59
Parameters
Name Optional Description
Date false Date to retrieve Second from
Examples
Second("12/8/2011"), Second(varDate), Second(OrderDateColumn)

SendMail

Description Allows the user to send an SMTP Message.
Syntax SendMail( Send_To_Emails, From_Email [, CC] [, Bcc] [, Subject] [, Body] [, Is_Body_HTML] [, Smtp_Server] [, Smtp_Port], [, Username] [, Password] [, Attachments] [, Priority])
Parameters
Name Optional Description
Send_To_Emails false A 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_Email false A string representing the E-mail Address the user wishes the E-mail to be sent from.
CC true Optional. 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 (;).
Bcc true Optional. 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 (;).
Subject true Optional. A string representing the subject for the Email.
Body true Optional. A string representing the Body for the Email
Is_Body_HTML true Optional. A boolean value representing whether the Body is formated in HTML.
Smtp_Server true Optional. A string value representing the name of the Server you wish to use to send the E-mail.
Smtp_Port true Optional. An integer value representing the Port required to send the E-mail through.
Username true Optional. A string value representing the username of the credentials required by the Smtp_Server to send the E-mail.
Password true Optional. A string value representing the password for the Username required by the Smtp_Server to send the E-mail.
Attachments true Optional. 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 (|).
Priority true Optional. A string value representing the Priority level of the E-mail. The Priority can be "High", "Low", or "Normal".

Sequence

Description Returns 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
Syntax Sequence(), Sequence_2, Sequence(Reference_Count, Start_Value, Increment, Max_Value, Cycle_Value)
Returns Numeric (Int64)
Parameters
Name Optional Description
Reference_Count true A variable that will store the value of the current sequence.
Start_Value true Integer. The staring point of the sequence value.
Increment true Integer. The value in which the sequence will be incremented.
Max_Value true Long. The maximum value of the sequence before the value is reset or an error is thrown based on the cycle parameter
Cycle_Value true Boolean. 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

Description Set a property on the connection manager.
Syntax SetConnectionProp( Connection_Manager, Property_Name, Property_Value )
Returns String.
Parameters
Name Optional Description
Connection_Manager false Connection manager to set a property on. Must use the @@[connection_manager_name] syntax.
Property_Name false String value. The property to set on the connection manager.
Property_Value false String value. The property value to set on the connection manager.
Examples
SetConnectionProp(@@[localhost.AdventureWorks], "ServerName", "localhost\sql2008")

SetDatePart

Description Set any part of supplied date/time to user defined value.
Syntax SetDatePart( Date, Format, Value)
Returns Date.
Parameters
Name Optional Description
Date false The value to add to the aggregation
Format false Date 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 />
Value false new value for specified date part.
Examples
SetDatePart(Now(),"D", 1)

SetVariable

Description Sets 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.
Syntax SETVARIABLE(@[Namespace::VariableName], Value )
Returns Current value of the variable. When value is NULL, the Integration Service returns the current value of $$Variable.
Parameters
Name Optional Description
VariableName false Namespace and Name of the mapping variable you want to set. Use with mapping variables with Max/Min aggregation type.
Value false The 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

Description Returns the value indicating the sign of a number.
Syntax Sign( Numeric_Value )
Returns Numeric
Parameters
Name Optional Description
Numeric_Value false The value to return the sign of
Examples
Sign(55)

Sin

Description Returns the sine of the specified angle.
Syntax Sin( Numeric_Value )
Returns Numeric
Parameters
Name Optional Description
Numeric_Value false Passes the values for which you want to the sine of
Examples
Sin(55)

Sinh

Description Returns the hyperbolic tangent of the specified angle.
Syntax Sinh( Numeric_Value )
Returns Numeric
Parameters
Name Optional Description
Numeric_Value false Passes the values for which you want to the sine of
Examples
Sinh(55)

Sleep

Description Pause the processing of a package for x milliseconds.
Syntax Sleep( Milliseconds_To_Sleep )
Returns NULL
Parameters
Name Optional Description
Milliseconds_To_Sleep false The number of milliseconds to pause processing.
Examples
Sleep(6000), Sleep(varNumberOfSecondsToSleep)

SoundEx

Description Encodes 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.
Syntax SOUNDEX( String )
Returns String. 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
Name Optional Description
String false Character string. Passes the string value you want to encode. You can enter any valid transformation expression.
Examples
SoundEx("Sentr"), SoundEx(varSoundEx)

Space

Description Returns a string containing a specified number of spaces.
Syntax Space( Number_Of_Spaces )
Returns String. NULL if a value passed to the function is NULL.
Parameters
Name Optional Description
Number_Of_Spaces false The number of spaces to create.
Examples
Space(10), Space(varNumberOfSpaces)

Sqrt

Description Returns the square root of a specified number.
Syntax Sqrt( Numeric_Value )
Returns Numeric
Parameters
Name Optional Description
Numeric_Value false Passes the values for which you want to the square root of
Examples
Sqrt(55)

StartsWith

Description StartsWith determines whether a string starts with a character or string value
Syntax StartsWith ( String_To_Search, Search_Value )
Returns Boolean. True if string_to_search ends with search_value. NULL if a value passed to the function is NULL.
Parameters
Name Optional Description
string false Character string. The string to search.
string false Character string. The value to find at the start of String_To_Search
Examples
StartsWith("400 College Dr", "Dr"), If StartsWith(AddressColumn, "400") Then ...)

SubStr

Description Returns a portion of a string. SUBSTR counts all characters, including blanks, starting at the beginning of the string.
Syntax SubStr( String, Start [, Length] )
Returns String. Empty string if you pass a negative or 0 length value. NULL if a value passed to the function is NULL.
Parameters
Name Optional Description
String false Must 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.
Start false Must 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.
Length true Must 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

Description Aggregation that calculates the total of all values. Can only be used as part of the Advanced Aggregation component
Syntax Sum( Value [, Condition])
Returns Double.
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 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

Description Returns the current date and time of the node hosting the Integration Service with precision to the nanosecond
Syntax SysTimeStamp( [Format] )
Returns Date.
Parameters
Name Optional Description
Format true Optional. 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 />