Task Factory Functions List S
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 /> |