Functions List

Parent Previous Next


Functions List


Abort

Description

Stops the dataflow and issues a specified error message to the dataflow log file.

Syntax

ABORT( String_Message [, Condition] )

Returns

NULL

Parameters

Name

Optional

Description

String_Message

false

The message you want to display in the dataflow log file when the dataflow stops. The string can be any length and you can enter any valid transformation expression.

Condition

false

(Optional) The conditional expression in which the data flow should be aborted.

Examples

Abort("Processing aborted"), Abort("Zero Length Detected In Name", Length(FirstName) = 0)

 

Abs

Description

Returns the absolute value of a numeric value

Syntax

ABS( [Numeric_Value] )

Returns

Positive Numeric value. ABS returns the same datatype as the numeric value passed in as an argument, can be NULL.

Parameters

Name

Optional

Description

Numeric_Value

false

Passes the values for which you want to return the absolute values. You can enter any valid transformation expression

 

Acos

Description

Returns the angle whose cosine is the specified number.

Syntax

ACOS( Numeric_Value )

Returns

Double value. NULL if a value passed to the function is NULL.

Parameters

Name

Optional

Description

Numeric_Value

false

Numeric datatype. A number representing a cosine, where number_value must be greater than or equal to -1, but less than or equal to 1. You can enter any valid task editor.

Examples

Acos(0.5), Acos(varValue)

 

AddToDate

Description

Returns a specified date with the specified number interval (integer) added to a specified datepart of that date. If you add or subtract value for specified datepart.

Syntax

AddToDate( Date, Format, Amount)

Returns

Returns modified date.

Parameters

Name

Optional

Description

Date

false

valid datetime value or expression

Format

false

Valid string format or expression for datepart you want to add/subtract. Supported format values are as below Year Format => Y, YYY, YY, YYYY Month Format => M, MM, MON, MONTH Day Format => D, DD, DDD, DY, DAY Hour Format => H, HH, HH12, HH24 Minute Format => MI, N Second Format => S, SS Milisecond Format => MS

Amount

false

Any valid integer value or expression specifying number of Year, Month, Day, Hour, Minute, Second or Milisecond. If you supply positive value then it will perform add else it will do subtract.

Examples

AddToDate( ORDER_DATE, 'DD', 3 ) //Adds 3 days to ORDER_DATE and return new datetime AddToDate( ORDER_DATE, 'HH', 14 ) //Adds 14 hours to ORDER_DATE and return new datetime AddToDate( ORDER_DATE, 'MM', -5 ) //Subtract 5 months from ORDER_DATE and return new datetime AddToDate( ORDER_DATE, 'YYYY', 1 ) //Add one year to supplied ORDER_DATE and return new datetime

 

AesDecrypt

Description

Returns decrypted data to string format using Advanced Encryption Standard (AES) algorithm with 128-bit encoding.

Syntax

AesDecrypt( Value, Key )

Returns

Returns a decrypted binary value that can be NULL if the input value is a null value.

Parameters

Name

Optional

Description

Value

false

The Binary datatype you wish to decrypt.

Key

false

The String of precision 16 characters or fewer that was originally used to encrypt the value. You can use mapping variables for the key.

Examples

AesDecrypt(varEncryptedData, "key-value-1234"), AesDecrypt(EncryptedValueColumn, "key-value-1234"), AesDecrypt(FileReadAllText("C:\data\encrypted.txt"), "key-value-1234")

 

AesEncrypt

Description

Returns data in encrypted format using Advanced Encryption Standard (AES) algorithm with 128-bit encoding.

Syntax

AesEncrypt( Value, Key )

Returns

Returns an encrypted binary value that can be NULL if the input is a null value.

Parameters

Name

Optional

Description

Value

false

The String value you wish to encrypt.

Key

false

The String of precision 16 characters or fewer that will be used to encrypt the value. You can use mapping variables for the key.

Examples

AesEncrypt("Encrypt this value", "key-value-1234"), AesEncrypt(PasswordColumn, "key-value-1234"), AesEncrypt(FileReadAllText("C:\data\unencrypted.txt"), "key-value-1234")

 

Asin

Description

Returns the angle whose sine is the specified number..

Syntax

Asin( Numeric_Value )

Returns

Double value. NULL if a value passed to the function is NULL.

Parameters

Name

Optional

Description

Numeric_Value

false

Numeric datatype. A number representing a sine, where number_value must be greater than or equal to -1, but less than or equal to 1. You can enter any valid task editor.

Examples

Acos(varValue)

 

Atan

Description

Returns the angle whose tangent is the specified number.

Syntax

Atan( Numeric_Value )

Returns

Numeric

Parameters

Name

Optional

Description

Numeric_Value

false

Passes the values for which you want to the Atan of

Examples

Atan(55)

 

Avg

Description

Aggregation that calculates the average of all values. Can only be used as part of the Advanced Aggregation component

Syntax

Avg( 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 Avg(SalesTotal, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida.

Examples

Avg(SalesTotal), Avg(SalesTotal, CustomerState="FL")

 

BinToDecimal

Description

Converts a binary value to an decimal value.

Syntax

BinToDecimal( Value )

Returns

Decimal

Parameters

Name

Optional

Description

Value

false

The binary value to convert.

Examples

BinToDecimal(10)

 

BinToHex

Description

Converts a binary value to an hexadecimal value.

Syntax

BinToHex( Value )

Returns

Decimal

Parameters

Name

Optional

Description

Value

false

The binary value to convert.

Examples

BinToHex(10)

 

BinToOct

Description

Converts a binary value to an octal value.

Syntax

BinToOct( Value )

Returns

Decimal

Parameters

Name

Optional

Description

Value

false

The binary value to convert.

Examples

BinToOct(10)

 

Ceiling

Description

Returns the smallest integer that is greater than or equal to the numeric value passed to this function.

Syntax

Ceiling( Numeric_Value )

Returns

Returns an integer if you pass a numeric value with declared precision between 0 and 28, otherwise it returns a Double. NULL values will return a NULL.

Parameters

Name

Optional

Description

Numeric_Value

false

Must be a numeric datatype. You can enter any valid transformation expression.

 

Choose

Description

Chooses a string from a list of strings based on a given position. You specify the position and the value. If the value matches the position, the Integration Service returns the value. You can either pass in comma delimeted static values or pass in a variable or column with delimeted values

Syntax

Choose( Index, String1 [, String2, ..., StringN] ), Choose( Index, Delimeted_String [, Delimeter_Value=","])

Returns

The string that matches the position of the index value. NULL if no string matches the position of the index value.

Parameters

Name

Optional

Description

Index

false

Numeric datatype. Enter a number based on the position of the value you want to match.

String

false

Any character value.

Examples

Choose(1, "PragmaticWorks, 400 College Drive, Middleburg, FL") - Will return "PragmaticWorks"
Choose(1, varPipeDelimetedAddress, "|")

 

Chr

Description

CHR returns the ASCII character corresponding to the numeric value you pass to this function. ASCII values fall in the range 0 to 255. You can pass any integer to CHR, but only ASCII codes 32 to 126 are printable characters.

Syntax

CHR( Numeric_Value )

Returns

ASCII or Unicode character. A string containing one character. NULL if a value passed to the function is NULL.

Parameters

Name

Optional

Description

Numeric_Value

false

Numeric datatype. The value you want to return as an ASCII or Unicode character. You can enter any valid transformation expression.

Examples

Chr(65), Chr(varCharacterValue)

 

ChrCode

Description

ChrCode returns the numeric ASCII value of the first character of the string passed to the function. ASCII values fall in the range 0 to 255.

Syntax

ChrCode ( String )

Returns

ASCII or Unicode character. A string containing one character. NULL if a value passed to the function is NULL.

Parameters

Name

Optional

Description

String

false

Character string. Passes the values you want to return as ASCII or Unicode values. You can enter any valid task editor.

Examples

ChrCode("A"), ChrCode("?")

 

Concat

Description

Concatenates two strings.

Syntax

CONCAT( First_String, Second_String )

Returns

String. NULL if both string values are NULL. Nulls If one of the strings is NULL, CONCAT ignores it and returns the other string. If both strings are NULL, CONCAT returns NULL.

Parameters

Name

Optional

Description

First_String

false

Any datatype except Binary. The first part of the string you want to concatenate. You can enter any valid task editor.

Second_String

false

Any datatype except Binary. The second part of the string you want to concatenate. You can enter any valid task editor.

Examples

Concat("Pragmatic", " ", "Works"), Concat(FirstNameColumn, " ", LastNameColumn)

 

ConvertBase

Description

Converts a integer base from one to another.

Syntax

ConvertBase( Value, From_Base, To_Base )

Returns

Integer.

Parameters

Name

Optional

Description

Value

false

The value to change the base of.

From_Base

false

The existing base of the integer. Valid values are 2, 8, 10, 16

To_Base

false

The new base of the integer. Valid values are 2, 8, 10, 16

Examples

ConvertBase("1234", 10, 2)

 

Cos

Description

Returns the cosine of a numeric value (expressed in radians).

Syntax

Cos( Numeric_Value )

Returns

Double value. NULL if a value passed to the function is NULL.

Parameters

Name

Optional

Description

Numeric_Value

false

Numeric datatype. Numeric data expressed in radians (degrees multiplied by pi divided by 180). Passes the values for which you want to calculate a cosine. You can enter any valid task editor.

Examples

Cos(10), Cos(varValue)

 

Cosh

Description

Returns the hyperbolic cosine of a numeric value (expressed in radians).

Syntax

Cosh( Numeric_Value )

Returns

Double value. NULL if a value passed to the function is NULL.

Parameters

Name

Optional

Description

Numeric_Value

false

Numeric datatype. Numeric data expressed in radians (degrees multiplied by pi divided by 180). Passes the values for which you want to calculate the hyperbolic cosine. You can enter any valid task editor.

Examples

Cosh(10), Cosh(varValue)

 

Count (*)

Description

Aggregation that calculates the total number based on the grouping. Can only be used as part of the Advanced Aggregation component

Syntax

Count( 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 Count(*, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida.

Examples

Count(*), Count(*, CustomerState="FL")

 

Count (column name)

Description

Aggregation that calculates the total number based on the grouping. Can only be used as part of the Advanced Aggregation component

Syntax

Count( Column_Name [, Condition])

Returns

Integer.

Parameters

Name

Optional

Description

Column_Name

false

The column to retrieve the value of 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 Count(CustomerID, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida.

Examples

Count(CustomerID), Count(CustomerID, CustomerState="FL")

 

DateCompare

Description

Compares two dates against each other.

Syntax

DateCompare( Date1, Date2 )

Returns

Integer. If Date1 is greater than Date2, 1 is returned. If Date1 equals Date2, 0 is returned. If Date1 is less than Date2, -1 is returned.

Parameters

Name

Optional

Description

Date1

false

The first date in the comparison.

Date2

false

The second date in the comparison.

Examples

DateCompare("7/4/2012", "7/5/2012"), DateCompare(varStartDate, varEndDate), DateCompare(OrderDateColumn, StartDateColumn)

 

DateDiff

Description

Returns the difference between two dates against each other.

Syntax

DateDiff( Date1, Date2, Format )

Returns

Integer. The difference based on datepart between the two days.

Parameters

Name

Optional

Description

Date1

false

The first date in the comparison.

Date2

false

The second date in the comparison.

Format

false

The datepart tells the DateDiff what part of the date to compare. <br /> 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 /> Microsecond = ""US"<br /> Week Of Year = "W", "WW"<br /> Quarter = "Q", "QQ"

Examples

DateDiff("7/4/2012", "7/5/2012", "d"), DateDiff(varStartDate, varEndDate, "M"), DateDiff(OrderDateColumn, StartDateColumn, "H")

 

DatePart

Description

Returns the part of the date specified in the datepart parameter.

Syntax

DatePart( Date, Date_Part )

Returns

Object.

Parameters

Name

Optional

Description

Date

false

The date used to retrieve the datepart from.

Date_Part

false

The datepart tells the DatePart what part of the date to retrieve. <br /> Year = "Y", "YY", "YYY", "YYYY"<br /> Day = "D", "DD", "DY", "DAY"<br /> Month = "M", "MM", "MON", "MONTH"<br /> Hour = "H", HH", "HH12", "HH24"<br /> Minute = "MI"<br /> Second = "SS""<br /> Millisecond = "MS"<br /> Microsecond = ""US"<br /> Day Of Week (1-7) = "WD","W" <br /> Week Of Year = "WW"<br /> Day Of Year = "DDD", "YD"<br /> Quarter = "Q"<br /> Julian Date = "J"

Examples

DatePart("7/4/2012", "d"), DatePart(varStartDate, "MM"), DatePart(OrderDateColumn, "HH")

 

Day

Description

Returns the Day from the date specified.

Syntax

Day( Date )

Returns

returns integer value of 1 - 31

Parameters

Name

Optional

Description

Date

false

Date to retrieve day from

Examples

Day("12/8/2011"), Day(varDate), Day(OrderDateColumn)

 

Day_Of_Week

Description

Returns the Day of the week from the date specified.

Syntax

Day_Of_Week( date )

Returns

returns integer value of 1 - 7

Parameters

Name

Optional

Description

Date

false

Date to retrieve day of week from

Examples

Day_Of_Week("12/8/2011"), Day_Of_Week(varDate), Day_Of_Week(OrderDateColumn)

 

DayName

Description

Returns the name of the day (Monday, Tuesday, etc) from the date specified.

Syntax

DayName( Date, Return_Shortname )

Returns

returns string. Name of the day

Parameters

Name

Optional

Description

Date

false

Date to retrieve day name from

Return_Shortname

false

Return the short name of the day (Mon, Tue, Wed)

Examples

DayName("12/8/2011", false), DayName(varDate, True), DayName(OrderDateColumn, false)

 

DayOfYear

Description

Returns the day of the year of the date specified.

Syntax

DayOfYear( Date )

Returns

returns integer value of 1 - 366

Parameters

Name

Optional

Description

Date

false

Date to retrieve day of year from

Examples

DayOfYear("12/8/2011"), DayOfYear(varDate), DayOfYear(OrderDateColumn)

 

DD_DELETE

Description

DD_DELETE is equivalent to the integer literal 2.

 

DD_INSERT

Description

DD_INSERT is equivalent to the integer literal 0.

 

DD_REJECT

Description

DD_REJECT is equivalent to the integer literal 3.

 

DD_UPDATE

Description

DD_INSERT is equivalent to the integer literal 1.

 

DecBase64

Description

Decodes a base 64 encoded value and returns a string with the binary data representation of the data. If you encode data using EncBase64, and you want to decode data using DecBase64, you must run the decoding dataflow using the same data movement mode. Otherwise, the output of the decoded data may differ from the original data.

Syntax

DecBase64( Value )

Returns

Binary decoded value. NULL if the input is a null value. Return values differ if you run the dataflow in Unicode mode versus ASCII mode.

Parameters

Name

Optional

Description

Value

false

String datatype. Data that you want to decode.

Examples

example goes here

 

DecimalToBin

Description

Converts a decimal value to an binary value.

Syntax

DecimalToBin( Value )

Returns

String

Parameters

Name

Optional

Description

value

false

The decimal value to convert.

Examples

DecimalToBin(10)

 

DecimalToHex

Description

Converts a decimal value to an hexadecimal value.

Syntax

DecimalToHex( Value )

Returns

String

Parameters

Name

Optional

Description

Value

false

The decimal value to convert.

Examples

DecimalToHex(10)

 

DecimalToOct

Description

Converts a decimal value to an octal value.

Syntax

DecimalToHex( Value )

Returns

Int64

Parameters

Name

Optional

Description

Value

false

The decimal value to convert.

Examples

DecimalToOct(10)

 

Decode

Description

Searches a string for a value you specify. If the function finds the value, it returns a result value, which you define. You can build an unlimited number of searches within a DECODE function.

Syntax

DECODE( Value, First_Search, First_Result [, Second_Search, Second_Result]...[, Default] )

Returns

First_result if the search finds a matching value. Default value if the search does not find a matching value. NULL if you omit the default argument and the search does not find a matching value. Even if multiple conditions are met, the Integration Service returns the first matching result. If the data contains multibyte characters and the DECODE expression compares string data, the return value depends on the code page and data movement mode of the Integration Service.

Parameters

Name

Optional

Description

Value

false

Any datatype except Binary. Passes the values you want to search. You can enter any valid task editor.

Search

false

Any value with the same datatype as the value argument. Passes the values for which you want to search. The search value must match the value argument. You cannot search for a portion of a value. Also, the search value is case sensitive. For example, if you want to search for the string 'Halogen Flashlight' in a particular port, you must enter 'Halogen Flashlight, not just 'Halogen'. If you enter 'Halogen', the search does not find a matching value. You can enter any valid task editor.

Result

false

Any datatype except Binary. The value you want to return if the search finds a matching value. You can enter any valid task editor.

Default

true

Any datatype except Binary. The value you want to return if the search does not find a matching value. You can enter any valid task editor.

Examples

Decode(ProductCode, "R", "Rims", "S", "Silver Lining", "M", "Mats")

 

DeleteFilesOlderThanNDays

Description

Deletes from from a directory that are N days old.

Syntax

DeleteFilesOlderThanNDays( Path, Pattern, Older_Than_Days, Recursive [, Continue_On_Failure] )

Returns

Integer. Returns the number of files deleted

Parameters

Name

Optional

Description

Path

false

The path where the file will be deleted from.

Pattern

false

The filter pattern for locating and deleting files. *.* = all files, *.xls= all xls files, 2012*.doc = any file that starts with 2012 and ends with .doc.

Older_Than_Days

false

The number of days that will be used to delete files older than.

Recursive

false

Delete files from subdirectories as well.

Continue_On_Failure

false

Continue deleting files even if one or more files fails to delete.

Examples

DeleteFilesOlderThanNDays("C:\ssis\test", "*.xml", 14, false, true)

 

DirCreate

Description

Creates a directory in the path supplied.

Syntax

DirCreate( Path )

Returns

Boolean. True if the Directory was created. False if it was not or path parameter is null

Parameters

Name

Optional

Description

Path

false

The path where the directory will be created.

Examples

DirCreate("C:\ssis\test")

 

DirDelete

Description

Removes a directory in the path supplied.

Syntax

DirDelete( Path )

Returns

Boolean. True if the Directory was removed. False if it was not or path parameter is null

Parameters

Name

Optional

Description

Path

false

The path where the directory will be removed from.

Examples

DirDelete("C:\ssis\test")

 

DirExists

Description

Check if the directory exists in the path supplied.

Syntax

DirExists( Path )

Returns

Boolean. True if the Directory exists. False if it does not or path parameter is null

Parameters

Name

Optional

Description

Path

false

The path where the directory is located.

Examples

DirExists("C:\ssis\test")

 

DirGetCreationTime

Description

Return the creation time of the directory.

Syntax

DirGetCreationTime( Path )

Returns

DateTime. If the path does not exist, the minimum date for your system is returned.

Parameters

Name

Optional

Description

Path

false

The path where the directory is located.

Examples

DirGetCreationTime("C:\ssis\")

 

DirGetLastAccessTime

Description

Return the time the directory was last accessed.

Syntax

DirGetLastAccessTime( Path )

Returns

DateTime. If the path does not exist, the minimum date for your system is returned.

Parameters

Name

Optional

Description

Path

false

The path where the directory is located.

Examples

DirGetLastAccessTime("C:\ssis\")

 

DirGetLastWriteTime

Description

Return the time the directory was last written to.

Syntax

DirGetLastWriteTime( Path )

Returns

DateTime. If the path does not exist, the minimum date for your system is returned.

Parameters

Name

Optional

Description

Path

false

The path where the directory is located.

Examples

DirGetLastWriteTime("C:\ssis\")

 

DirGetRoot

Description

Returns the root directory of the path specified.

Syntax

DirGetRoot( Path )

Returns

String. Null if the path parameter is Null

Parameters

Name

Optional

Description

Path

false

The path where the directory is located.

Examples

DirGetRoot("C:\ssis\test")

 

DirMove

Description

Moves a directory in from one path to another.

Syntax

DirMove( Path_Source, Path_Destination )

Returns

Boolean. True if the Directory was moved. False if it was not or either the Path_Source or Path_Destination parameters are null

Parameters

Name

Optional

Description

Path_Source

false

The path where the directory will be moved from.

Path_Destination

false

The path where the directory will be moved to.

Examples

DirMove("C:\ssis\test", "C:\ssis\test1")

 

DirSetCreationTime

Description

Set the time the directory was created.

Syntax

DirSetCreationTime( Path, Date_Created )

Returns

Boolean. True if the value is set. False if either parameter is null.

Parameters

Name

Optional

Description

Path

false

The path where the directory is located.

Date_Created

false

The date the directory was created.

Examples

DirSetCreationTime("C:\ssis\", "7/6/2012 14:24")

 

DirSetLastAccessTime

Description

Set the time the directory was last accessed.

Syntax

DirSetLastAccessTime( Path, Date_Last_Access )

Returns

Boolean. True if the value is set. False if either parameter is null.

Parameters

Name

Optional

Description

Path

false

The path where the directory is located.

Date_Last_Access

false

The date the directory was last accessed.

Examples

DirSetLastAccessTime("C:\ssis\", "7/6/2012 14:24")

 

DirSetLastWriteTime

Description

Set the time the directory was written to accessed.

Syntax

DirSetLastWriteTime( Path, Date_Last_Written )

Returns

Boolean. True if the value is set. False if either parameter is null.

Parameters

Name

Optional

Description

Path

false

The path where the directory is located.

Date_Last_Written

false

The date the directory was last written to.

Examples

DirSetLastWriteTime("C:\ssis\", "7/6/2012 14:24")

 

EncBase64

Description

Encodes data by converting binary data to string data using Multipurpose Internet Mail Extensions (MIME) encoding. Encode data when you want to store data in a database or file that does not allow binary data.

Syntax

EncBase64( Value )

Returns

Encoded value. NULL if the input is a null value.

Parameters

Name

Optional

Description

Value

false

Binary or String datatype. Data that you want to encode.

Examples

example goes here

 

EndsWith

Description

EndsWith determines whether a string ends with a character or string value

Syntax

EndsWith ( 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_To_Search

false

Character string. The string to search.

Search_Value

false

Character string. The value to find at the end of String_To_Search

Examples

EndsWith("400 College Dr", "Dr"), If EndsWith(AddressColumn, "Dr") Then ...)

 

Error

Description

Causes the Integration Service to skip a row and issue an error message, which you define. The error message displays in the progress log.

Syntax

ERROR( String )

Returns

String.

Parameters

Name

Optional

Description

String

false

String value. The message you want to display when the Integration Service skips a row based on the expression containing the ERROR function. The string can be any length.

Examples

Error("Whoops! Something went wrong!")

 

ExecuteSQL

Description

Executes supplied sql statement or stored procedure and returns single value or full resultset depending on column_name_or_index setting. By default it returns first column of first row in the resultset. If you execute DDL (e.g. CREATE TABLE...) or DML (UPDATE/DELETE...) statements which doesnt return anything then it will return NULL. You can use this function to call simple or parameterized SELECT queries or stored procs. You can also call DDL/DML statemets (e.g. UPDATE/DELETE/ALTER/INSERT/TRUNCATE)

Syntax

ExecuteSQL( connection_manager, sql_statement [, is_stored_procedure] [, command_timeout] [, column_name_or_index] [, param1, param2 ... paramN])

Returns

Returns single value or full resultset as ADO.net DataTable. If no result set or value found (e.g. UPDATE/DELETE Statement) then returns NULL

Parameters

Name

Optional

Description

Connection_Manager

false

The Connection Manager which will use to execute provided sql_statement. It has to be either OLEDB or ADO.net connection manager. Syntax for connection manager is @@[connectionname]. When you use OLEDB connection to execute queries then you cant define named parameters (e.g. @mypara ). You have to use "?" for parameter (Example: select * from customer where customerid=? ). When you use ADO.net connection type then you can use named parameters in your queries (e.g. select * from customer where customerid=@in_customer)

Sql_Statement

false

String value used representing the Stored Procedure that will be executed.

Is_Stored_Procedure

true

Boolean value indicating whether the Sql_Statement is a Stored Procedure or not. When this parameter is True then you dont have to include parameters as part of sql_statement. (Default=False)

Command_Timeout

true

Timeout in seconds for sql command. 0 means unlimited (Default=0)

Column_Name_OR_Index

true

If you have more than one column in output rowset and you want to return other than first column then specify column by name or Index (starting from 0). If you pass "-1" (in double quotes) then it will return full result set as ADO.net DataTable which you can store in object datatype variable and consume it later in script task or use it in ForEach Loop

Parameters

true

List of parameters for supplied sql statement. If sqlstatement/stored proc doesn't require paramets then ommit this argument. Parameters are passed using below functions. InParam(paraName [,DataType] [,Precision/Length] [,Scale] , Value ) : This function defines Input parameter OutParam(paraName ,DataType ,Precision/Length , Scale , variableNameOrVariablePortName) : This function defines Output parameter Examples: ----------------------- OLEDB CONNECTION EXAMPLE ----------------------- --Single Input and output Parameter for OLEDB connection (Use ? for parameter indicator. Nmaed parameters not supported with OLEDB). Returns value back to User Variable "var2" ExecuteSQL(@@[NorthwindOLEDB], "SELECT ?=COUNT(*) FROM ORDERS WHERE CUSTOMERID=?" ,FALSE ,OutParam("out1",DbType.Int32,0,0,"User::var2") -- See variable name is in Double quotes.. don't pass as @[var2] ,InParam("prod1","ALFKI") ) ----------------------- ADO.NET CONNECTION EXAMPLE ----------------------- --Single Input and output Parameter for ADO.net connection (Use @ prefix for named parameters). Returns value back to Variable Column V_COUNT ( only for DataFlow transforms) ExecuteSQL(@@[NorthwindOLEDB], "SELECT @MyCount=COUNT(*) FROM ORDERS WHERE CUSTOMERID=@pCustID " ,FALSE ,OutParam("MyCount",DbType.Int32,0,0, V_COUNT) ---See column name is not in DOUBLE QUOTES. This column must be variable column (Not output column) ,InParam("pCustID","ALFKI") ) Here is list of supported datatypes which can be used with InParam or OutParam functions http://msdn.microsoft.com/en-us/library/system.data.dbtype.aspx AnsiString = 0, Binary = 1, Byte = 2, Boolean = 3, Currency = 4, Date = 5, DateTime = 6, Decimal = 7, Double = 8, Guid = 9, Int16 = 10, Int32 = 11, Int64 = 12, Object = 13, SByte = 14, Single = 15, String = 16, Time = 17, UInt16 = 18, UInt32 = 19, UInt64 = 20, VarNumeric = 21, AnsiStringFixedLength = 22, StringFixedLength = 23, Xml = 25, DateTime2 = 26, DateTimeOffset = 27

Examples

======================================== Examples (Calling DML/DDL Statements e.g. CREATE/UPDATE/INSERT/DELETE): ======================================== --Call simple sql statement which doesn't contain any paramter ExecuteSQL(@@[NorthwindOLEDB] , "DELETE FROM Customer Where Country='USA'" ) --Call simple create table ExecuteSQL(@@[NorthwindOLEDB] , "CREATE TABLE MyTable( col1 int, col2 int)" ) ======================================== Examples (Returning data from stored proc or select query): ======================================== --Return first column of first row ExecuteSQL(@@[NorthwindOLEDB] , "SELECT * FROM Customer Where Country='USA'" ) --Return "Phone" column from 1st row of resultset [Use of Column Name] ExecuteSQL(@@[NorthwindOLEDB] , "SELECT * FROM Customer Where Country='USA'" ,False,0, "Phone") --Call stored proc and return "data" column from first row of sp_spaceused output ExecuteSQL(@@[NorthwindOLEDB] , "exec sp_spaceused 'dbo.Customers'" ,False,0, "data") --Return 5th column from 1st row of resultset [Use of ColumnIndex] ExecuteSQL(@@[NorthwindOLEDB] , "SELECT * FROM Customer Where Country='USA'" ,False,0, 5) --Return Full Datatable (Call from ExpressionTask. Bind Expression with variable with object datatype to store full DataTable) ExecuteSQL(@@[NorthwindOLEDB] , "SELECT * FROM Customer Where Country='USA'" ,False,0, "-1") --Timeout query if it takes more than 30 seconds ExecuteSQL(@@[NorthwindOLEDB] , "SELECT * FROM Customer Where Country='USA'" ,False,30) ======================================== Parameter Examples (OLEDB Connection): ======================================== --Single Input Parameter for OLEDB connection ExecuteSQL(@@[NorthwindOLEDB] , "SELECT * FROM Customer Where Country=? " ,False, InParam("para1",DbType.String,40,0, "USA" ) ) --Single Input Parameter for OLEDB connection (Don't pass datatype info.. and let it guess)… If it can't guess it woll use String(4000) ExecuteSQL(@@[NorthwindOLEDB] , "SELECT * FROM Customer Where Country=? " ,False, InParam("para1", "USA" ) ) --Single Input and output Parameter for OLEDB connection. Returns value back to User Variable "var2" ExecuteSQL(@@[NorthwindOLEDB], "SELECT ?=COUNT(*) FROM ORDERS WHERE CUSTOMERID=?" ,FALSE ,OutParam("out1",DbType.Int32,0,0,"User::var2") -- See variable name is in Double quotes.. don't pass as @[var2] ,InParam("prod1","ALFKI") ) --Return value from stored procedure (RETURN VALUE always numeric and output from stored proc via RETURN statement) --Single output Parameter for OLEDB connection. Returns value back to User Variable "var2" ExecuteSQL(@@[NorthwindOLEDB], "exec ?=sp_MyStoredProc" ,FALSE ,OutParam("out1",DbType.Int32,0,0,"User::var2") -- See variable name is in Double quotes.. don't pass as @[var2]. ) --Single Input and output Parameter for OLEDB connection. Returns value back to Variable column V_COUNT ( only for DataFlow transforms) ExecuteSQL(@@[NorthwindOLEDB], "SELECT @MyCount=COUNT(*) FROM ORDERS WHERE CUSTOMERID=@pCustID " ,FALSE ,OutParam("MyCount",DbType.Int32,0,0, V_COUNT) ---See port name is not in DOUBLE QUOTES ,InParam("pCustID","ALFKI") ) ======================================== Parameter Examples (ADO.net Connection): ======================================== --Single Input Parameter for ADONET connection ExecuteSQL(@@[NorthwindADONET] , "SELECT * FROM Customer Where Country=@pCountry" ,False, InParam("pCountry ",DbType.String,40,0, "USA" ) ) --Single Input and output Parameter for ADONET connection. Returns value back to User Variable "var2" ExecuteSQL(@@[NorthwindADONET], "SELECT @MyCount=COUNT(*) FROM ORDERS WHERE CUSTOMERID=@pCustID " ,FALSE ,OutParam("MyCount",DbType.Int32,0,0,"User::var2") ,InParam("pCustID","ALFKI") ) --Single Input and output Parameter for ADONET connection. Returns value back to Variable column V_COUNT ( only for DataFlow transforms) ExecuteSQL(@@[NorthwindADONET], "SELECT @MyCount=COUNT(*) FROM ORDERS WHERE CUSTOMERID=@pCustID " ,FALSE ,OutParam("MyCount",DbType.Int32,0,0, V_COUNT) ,InParam("pCustID","ALFKI") )

 

Exp

Description

Returns Euler's Number, e, raised to the specified power (Exponent), where e=2.71828183. For example, EXP(2) returns 7.38905609893065. You might use this function to analyze scientific and technical data rather than business data. EXP is the reciprocal of the LN function, which returns the natural logarithm of a numeric value.

Syntax

EXP( Exponent )

Returns

Double value. NULL if a value passed as an argument to the function is NULL.

Parameters

Name

Optional

Description

Exponent

false

Numeric datatype. The value to which you want to raise e. The exponent in the equation e^value. You can enter any valid task editor.

Examples

EXP( ExponentColumn )

 

FileAppendAllText

Description

Append text to the end of a file contents.

Syntax

FileAppendAllText( Path, Text [, Encoding])

Returns

Boolean. If the path does not exist, null is returned.

Parameters

Name

Optional

Description

Path

false

The path where the file is located.

Text

false

Text to append to file contents.

Encoding

true

Encoding to use to write to file. Choices are ASCII, UNICODE, UTF7, UTF8, UTF32 or Default.

Examples

FileAppendAllText("C:\ssis\test.xml", "Wrox Professional SSIS 2012")

 

FileChangeExtension

Description

Changes the file extension from one to another.

Syntax

FileChangeExtension( Path, New_Extension )

Returns

String. The path to the file with the new extension.

Parameters

Name

Optional

Description

Path

false

The path to the file.

New_Extension

false

The new extension of the file with or without the period. Pass in null to remove the extension.

Examples

FileChangeExtension("C:\ssis\test.xml", "txt")

 

FileCombinePath

Description

Combines the path of files together. It handles all of the checking for slashes and adds or removes them.

Syntax

FileCombinePath( Path1, Path2 )

Returns

String. The combination of path1 and path2 with all slashes added or removed.

Parameters

Name

Optional

Description

Path1

false

The first part of the path.

Path2

false

The second part of the path.

Examples

FileCombinePath("C:\ssis", "test.xml")

 

FileCopy

Description

Copy a file from one location to another .

Syntax

FileCopy( Source, Destination [, Overwrite])

Returns

Boolean. If the copy succeeds. Null of either parameter is null or either the source file doesn't exist.

Parameters

Name

Optional

Description

Source

false

The source path file path.

Destination

false

The destination file path.

Overwrite

false

Overwrite the existing file?.

Examples

FileCopy("C:\ssis\test.xml", "C:\ssis\processedfiles\test.xml")

 

FileDelete

Description

Delete a file in the path supplied.

Syntax

FileDelete( Path )

Returns

Boolean. True if the file was removed. False if it was not or path parameter is null

Parameters

Name

Optional

Description

Path

false

The path where the file will be removed from.

Examples

FileDelete("C:\ssis\test")

 

FileExists

Description

Check if the file exists in the path supplied.

Syntax

FileExists( Path )

Returns

Boolean. True if the file exists. False if it does not or path parameter is null

Parameters

Name

Optional

Description

Path

false

The path where the file is located.

Examples

FileExists("C:\ssis\test.xml")

 

FileGetCreationTime

Description

Return the creation time of the file.

Syntax

FileGetCreationTime( Path )

Returns

DateTime. If the path does not exist, the minimum date for your system is returned.

Parameters

Name

Optional

Description

Path

false

The path where the file is located.

Examples

FileGetCreationTime("C:\ssis\test.xml")

 

FileGetDirectoryName

Description

Get the directory of a file path.

Syntax

FileGetDirectoryName( Path )

Returns

String

Parameters

Name

Optional

Description

Path

false

The path to the file.

Examples

FileGetDirectoryName("C:\ssis\test.xml")

 

FileGetExtension

Description

Get the extension of a file.

Syntax

FileGetExtension( Path )

Returns

String

Parameters

Name

Optional

Description

Path

false

The path to the file.

Examples

FileGetExtension("C:\ssis\test.xml")

 

FileGetFullPath

Description

Get the full path to a file.

Syntax

FileGetFullPath( Path )

Returns

String

Parameters

Name

Optional

Description

Path

false

The path to the file.

Examples

FileGetFullPath("C:\ssis\test.xml")

 

FileGetLastAccessTime

Description

Return the time the file was last accessed.

Syntax

FileGetLastAccessTime( Path )

Returns

DateTime. If the path does not exist, the minimum date for your system is returned.

Parameters

Name

Optional

Description

Path

false

The path where the file is located.

Examples

FileGetLastAccessTime("C:\ssis\test.xml")

 

FileGetLastWriteTime

Description

Return the time the file was last written to.

Syntax

FileGetLastWriteTime( Path )

Returns

DateTime. If the path does not exist, the minimum date for your system is returned.

Parameters

Name

Optional

Description

Path

false

The path where the file is located.

Examples

FileGetLastWriteTime("C:\ssis\test.xml")

 

FileGetName

Description

Get the name of file from a file path.

Syntax

FileGetName( Path )

Returns

String

Parameters

Name

Optional

Description

Path

false

The path to the file.

Examples

FileGetName("C:\ssis\test.xml")

 

FileGetNameWithoutExtension

Description

Get the extension of file from a file path.

Syntax

FileGetNameWithoutExtension( Path )

Returns

String

Parameters

Name

Optional

Description

Path

false

The path to the file.

Examples

FileGetNameWithoutExtension("C:\ssis\test.xml")

 

FileGetRandomFileName

Description

Get a random file name without a path

Syntax

FileGetRandomFileName( )

Returns

String

Examples

FileGetRandomFileName()

 

FileGetTempFileName

Description

Get the name of a temporary file with a path to the temporary directory.

Syntax

FileGetTempFileName( )

Returns

String

Examples

FileGetTempFileName()

 

FileGetTempPath

Description

Get the path to the temporary files directory.

Syntax

FileGetTempPath( )

Returns

String

Examples

FileGetTempPath()

 

FileHasExtension

Description

Determine whether a file has an extension.

Syntax

FileHasExtension( Path )

Returns

Boolean

Parameters

Name

Optional

Description

Path

false

The path to the file.

Examples

FileHasExtension("C:\ssis\test"), FileHasExtension("C:\ssis\test.xml")

 

FileInUse

Description

Check if the file is in use for the path supplied.

Syntax

FileInUse( Path )

Returns

Boolean. True if the file is in use. False if it is not in use or path parameter is null

Parameters

Name

Optional

Description

Path

false

The path where the file is located.

Examples

FileInUse("C:\ssis\test.xml")

 

FileIsPathRooted

Description

Gets a value indicating whether the specified path string contains a root.

Syntax

FileIsPathRooted( Path )

Returns

Boolean

Parameters

Name

Optional

Description

path

false

The path to the file.

Examples

FileIsPathRooted("C:\ssis\test"), FileIsPathRooted("test.xml")

 

FileMove

Description

Move a file from one location to another.

Syntax

FileMove( Source, Destination)

Returns

Boolean. If the move succeeds. Null of either parameter is null or either the source file doesn't exist.

Parameters

Name

Optional

Description

Source

false

The source path file path.

Destination

false

The destination file path.

Examples

FileMove("C:\ssis\test.xml", "C:\ssis\processedfiles\test.xml")

 

FileOrDirectoryGetPathRoot

Description

Get the root path to a file.

Syntax

FileOrDirectoryGetPathRoot( Path )

Returns

String

Parameters

Name

Optional

Description

Path

false

The path to the file.

Examples

FileOrDirectoryGetPathRoot("C:\ssis\test.xml"), FileOrDirectoryGetPathRoot("C:\ssis\test\")

 

FileReadAllBytes

Description

Return the bytes from a specified file.

Syntax

FileReadAllBytes( Path )

Returns

Byte Array. If the path does not exist, null is returned.

Parameters

Name

Optional

Description

Path

false

The path where the file is located.

Examples

FileReadAllBytes("C:\ssis\test.xml")

 

FileReadAllText

Description

Return the text from a specified file.

Syntax

FileReadAllText( Path )

Returns

String. If the path does not exist, null is returned.

Parameters

Name

Optional

Description

Path

false

The path where the file is located.

Examples

FileReadAllText("C:\ssis\test.xml")

 

FileReplace

Description

Copy a file from one location to another and create a backup of the destination .

Syntax

FileReplace( Source, Destination, Backup_Destination )

Returns

Boolean. If the copy succeeds. Null of either parameter is null or either the source file doesn't exist.

Parameters

Name

Optional

Description

Source

false

The source path file path.

Destination

false

The destination file path.

Backup_Destination

false

The path where the backup file will be created.

Examples

FileReplace("C:\ssis\test.xml", "C:\ssis\processedfiles\test.xml", "C:\ssis\processedfiles\backup\test.xml")

 

FileSetCreationTime

Description

Set the time the file was created.

Syntax

FileSetCreationTime( Path, Date_Created )

Returns

Boolean. True if the value is set. False if either parameter is null.

Parameters

Name

Optional

Description

Path

false

The path where the file is located.

Date_Created

false

The date the file was created.

Examples

FileSetCreationTime("C:\ssis\test.xml", "7/6/2012 14:24")

 

FileSetLastAccessTime

Description

Set the time the file was last accessed.

Syntax

FileSetLastAccessTime( Path, Date_Last_Access )

Returns

Boolean. True if the value is set. False if either parameter is null.

Parameters

Name

Optional

Description

Path

false

The path where the file is located.

Date_Last_Access

false

The date the file was last accessed.

Examples

FileSetLastAccessTime("C:\ssis\test.xml", "7/6/2012 14:24")

 

FileSetLastWriteTime

Description

Set the time the file was written to accessed.

Syntax

FileSetLastWriteTime( Path, Date_Last_Written )

Returns

Boolean. True if the value is set. False if either parameter is null.

Parameters

Name

Optional

Description

Path

false

The path where the file is located.

Date_Last_Written

false

The date the file was last written to.

Examples

FileSetLastWriteTime("C:\ssis\test.xml", "7/6/2012 14:24")

 

FileWriteAllBytes

Description

Write bytes to the file contents.

Syntax

FileWriteAllBytes( Path, Bytes [, Encoding])

Returns

Boolean. If the path does not exist, null is returned.

Parameters

Name

Optional

Description

Path

false

The path where the file is located.

Bytes

false

Bytes to write to the file.

Encoding

true

Encoding to use to write to file. Choices are ASCII, UNICODE, UTF7, UTF8, UTF32 or Default.

Examples

FileWriteAllBytes("C:\ssis\test.xml", varBytes, "UTF8")

 

FileWriteAllText

Description

Write text to the file contents.

Syntax

FileWriteAllText( Path, Text [, Encoding])

Returns

Boolean. If the path does not exist, null is returned.

Parameters

Name

Optional

Description

Path

false

The path where the file is located.

Text

false

Text to write to the file.

Encoding

true

Encoding to use to write to file. Choices are ASCII, UNICODE, UTF7, UTF8, UTF32 or Default.

Examples

FileWriteAllText("C:\ssis\test.xml", "Wrox Professional SSIS 2012")

 

First

Description

Aggregation that returns the first value of the group. Can only be used as part of the Advanced Aggregation component

Syntax

First( Value [, Condition])

Returns

Object.

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 First(SalesTotal, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida.

Examples

First(SalesTotal), First(SalesTotal, CustomerState="FL")

 

FirstDate

Description

Returns the first day of a specified month.

Syntax

FirstDate(), FirstDate( Date )

Returns

returns date

Parameters

Name

Optional

Description

Date

false

Date to retrieve value from

Examples

FirstDate(), FirstDate("12/8/2011"), FirstDate(varDate), FirstDate(OrderDateColumn)

 

FirstDateOfNextMonth

Description

Returns the first day of a next month.

Syntax

FirstDateOfNextMonth(), FirstDateOfNextMonth( Date )

Returns

returns date

Parameters

Name

Optional

Description

Date

false

Date to retrieve value from

Examples

FirstDateOfNextMonth(), FirstDateOfNextMonth("12/8/2011"), FirstDateOfNextMonth(varDate), FirstDateOfNextMonth(OrderDateColumn)

 

Floor

Description

Returns the largest integer less than or equal to the numeric value you pass to this function. For example, if you pass 3.14 to FLOOR, the function returns 3. If you pass 3.98 to FLOOR, the function returns 3. Likewise, if you pass -3.17 to FLOOR, the function returns -4.

Syntax

FLOOR( Numeric_Value )

Returns

Integer if you pass a numeric value with declared precision between 0 and 28. Double if you pass a numeric value with declared precision greater than 28. NULL if a value passed to the function is NULL.

Parameters

Name

Optional

Description

Numeric_Value

false

Numeric datatype. You can enter any valid task editor as long as it evaluates to numeric data.

Examples

example goes here

 

FormatCurrency

Description

Returns an expression formatted as currency

Syntax

FormatCurrency( Expression [, Digits_After_Decimal] [, Include_Leading_Digit] [, Use_Parens_For_Negative_Numbers] [, Group_Digits])

Returns

String. Returns an expression formatted as a currency.

Parameters

Name

Optional

Description

Expression

false

Expression to be formatted.

Digits_After_Decimal

false

Numeric value indicating how many places are displayed to the right of the decimal. The default value is –1, which indicates that the computer's regional settings are used.

Include_Leading_Digit

false

Indicates whether a leading 0 is displayed for fractional values. Values are 0 for False, -1 for True

Use_Parens_For_Negative_Numbers

false

Indicates whether to place negative values within parentheses. Values are 0 for False, -1 for True

Group_Digits

false

Indicates whether or not numbers are grouped using the group delimiter specified in the locale settings. Values are 0 for False, -1 for True

Examples

FormatCurrency("4589.932", 2)

 

FormatDateTime

Description

Returns an expression formatted as a date

Syntax

FormatDateTime( Expression [, Date_Format] )

Returns

String. Returns an expression formatted as a date.

Parameters

Name

Optional

Description

Expression

false

Date expression to be formatted.

Date_Format

false

Numeric value that indicates the date/time format used. If omitted, DateFormat.GeneralDate is used. Values are<br /> General Date = 0<br /> Long Date = 1<br /> Short Date = 2 <br /> Long Time = 3<br /> Short Time = 4

Examples

FormatDateTime(GetDate(), 2)

 

FormatNumber

Description

Returns an expression formatted as a number

Syntax

FormatNumber( Expression [, Digits_After_Decimal] [, Include_Leading_Digit] [, Use_Parens_For_Negative_Numbers] [, Group_Digits])

Returns

String. Returns an expression formatted as a number.

Parameters

Name

Optional

Description

Expression

false

Expression to be formatted.

Digits_After_Decimal

false

Numeric value indicating how many places are displayed to the right of the decimal. The default value is –1, which indicates that the computer's regional settings are used.

Include_Leading_Digit

false

Indicates whether a leading 0 is displayed for fractional values. Values are 0 for False, -1 for True

Use_Parens_For_Negative_Numbers

false

Indicates whether to place negative values within parentheses. Values are 0 for False, -1 for True

Group_Digits

false

Indicates whether or not numbers are grouped using the group delimiter specified in the locale settings. Values are 0 for False, -1 for True

Examples

FormatNumber("4589.932", 2)

 

FormatPercent

Description

Returns an expression formatted as a percentage.

Syntax

FormatPercent( Expression [, Digits_After_Decimal] [, Include_Leading_Digit] [, Use_Parens_For_Negative_Numbers] [, Group_Digits])

Returns

String. Returns an expression formatted as a percentage.

Parameters

Name

Optional

Description

Expression

false

Expression to be formatted.

Digits_After_Decimal

false

Numeric value indicating how many places are displayed to the right of the decimal. The default value is –1, which indicates that the computer's regional settings are used.

Include_Leading_Digit

false

Indicates whether a leading 0 is displayed for fractional values. Values are 0 for False, -1 for True

Use_Parens_For_Negative_Numbers

false

Indicates whether to place negative values within parentheses. Values are 0 for False, -1 for True

Group_Digits

false

Indicates whether or not numbers are grouped using the group delimiter specified in the locale settings. Values are 0 for False, -1 for True

Examples

FormatPercent("4589.932", 2)

 

FV

Description

Returns the future value of an investment, where you make periodic, constant payments and the investment earns a constant interest rate.

Syntax

FV( Rate, Terms, Payment [, Present_Value] [, Type] )

Returns

Numeric.

Parameters

Name

Optional

Description

Rate

false

Numeric. Interest rate earned in each period. Expressed as a decimal number. Divide the percent rate by 100 to express it as a decimal number. Must be greater than or equal to 0.

Terms

false

Numeric. Number of periods or payments. Must be greater than 0.

Payment

false

Numeric. Payment amount due per period. Must be a negative number.

Present_Value

true

Numeric. Current value of the investment. If you omit this argument, FV uses 0.

Type

true

Integer. Timing of the payment. Enter 1 if payment is at the beginning of period. Enter 0 if payment is at the end of period. Default is 0. If you enter a value other than 0 or 1, the Integration Service treats the value as 1.

Examples

FV(Ratevar, Termvar, Paymentvar), FV(3.25, 36, 125.50, 1200, 1)

 

GetChar

Description

GetChar returns the character at a specified index in a string.

Syntax

GetChar( String_Value, Index )

Returns

Single character. NULL if a value passed to the function is NULL.

Parameters

Name

Optional

Description

String_Value

false

Character string. The string value to return a character from.

Index

false

Character string. The index of the character you want returned from the String_Value parameter .

Examples

GetChar("GetTheC", 7), If GetChar(AddressColumn, 1) = "A" Then ...

 

GetConnectionProp

Description

Get a property from a connection manager.

Syntax

GetConnectionProp( Connection_Manager, Property_Name )

Returns

String.

Parameters

Name

Optional

Description

Connection_Manager

false

Connection manager to retrieve a property from. Must use the @@[connection_manager_name] syntax.

Property_Name

false

String value. The property to retrieve from the connection manager.

Examples

GetConnectionProp(@@[localhost.AdventureWorks], "ServerName")

 

GetDate

Description

Returns the current date and time.

Syntax

GetDate()

Returns

Date

Examples

GetDate()

 

GetDatePart

Description

Returns the part of the date specified in the datepart parameter.

Syntax

GetDatePart( Date, Date_Part )

Returns

Object.

Parameters

Name

Optional

Description

Date

false

The date used to retrieve the datepart from.

Date_Part

false

The datepart tells the DatePart what part of the date to retrieve. <br /> The datepart tells the DatePart what part of the date to retrieve. <br /> Year = "Y", "YY", "YYY", "YYYY"<br /> Day = "D", "DD", "DDD", "DY", "DAY"<br /> Month = "MM", "MON", "MONTH"<br /> Hour = "HH", "HH12", "HH24"<br /> Minute = "MI"<br /> Second = "SS""<br /> Millisecond = "MS"<br /> Microsecond = ""US"<br /> Day Of Week (1-7) = "WD","W" <br /> Week Of Year = "WW"<br /> Day Of Year = "YD"<br /> Quarter = "Q"<br /> Julian Date = "J"

Examples

GetDatePart("7/4/2012", "d"), GetDatePart(varStartDate, "MM"),GetDatePart(OrderDateColumn, "HH")

 

GetDiskFreeSpace

Description

Returns the amount of free disk space in megabytes.

Syntax

GetDiskFreeSpace( Drive_Letter )

Returns

Integer. The amount of free disk space in megabytes

Parameters

Name

Optional

Description

Drive_Letter

false

The drive letter to determine disk space. Must include :\ with the drive letter (e.g. C:\).

Examples

GetDiskFreeSpace("C:\")

 

GetDiskSize

Description

Returns the amount of disk space for a drive in megabytes.

Syntax

GetDiskSize( Drive_Letter )

Returns

Integer. The amount of disk space in megabytes

Parameters

Name

Optional

Description

Drive_Letter

false

The drive letter to determine disk space. Must include :\ with the drive letter (e.g. C:\).

Examples

GetDiskSize("C:\")

 

GetUtcDate

Description

Returns the current utc date and time.

Syntax

GetUtcDate()

Returns

Date

Examples

GetUtcDate()

 

Greatest

Description

Returns the greatest value from a list of input values. Use this function to return the greatest string, date, or number. By default, the match is case sensitive.

Syntax

GREATEST( Case_Flag, Value1 [, Value2, ..., ValueN,] )

Returns

value1 if it is the greatest of the input values, value2 if it is the greatest of the input values, and so on. NULL if any of the arguments is null.

Parameters

Name

Optional

Description

Value

false

Any datatype except Binary. Datatype must be compatible with other values. Value you want to compare against other values. You must enter at least one value argument. If the value is numeric, and other input values are numeric, all values use the highest precision possible. For example, if some values are Integer datatype and others are Double datatype, the Integration Service converts the values to Double.

Case_Flag

false

Must be an integer. Determines whether the arguments in this function are case sensitive. You can enter any valid task editor. When CaseFlag is a number other than 0, the function is case sensitive. When CaseFlag is a null value or 0, the function is not case sensitive.

Examples

Greatest(false, varValues), Greatest(false, "test1", "test2", "test3")

 

GroupNum

Description

Returns the group of which this aggregate row belongs to. For instance, if there were 10 rows outputted from the Advanced Aggregate, then GroupNum would add 1-10 for each respective row

Syntax

GroupNum()

Returns

Integer

Examples

GroupNum()

 

GroupRowNum

Description

Returns the number of rows that were used in this aggregation group.

Syntax

GroupRowNum()

Returns

Integer

Examples

GroupRowNum()

 

Hex

Description

Convert a value to Hex value.

Syntax

Hex( Value )

Returns

String. Returns the Hex value of the integer.

Parameters

Name

Optional

Description

Value

false

The integer value to convert to Hex.

Examples

Hex(18), Hex(varValue)

 

HexToBin

Description

Converts a hexical value to a binary value.

Syntax

HexToBin( Value )

Returns

String

Parameters

Name

Optional

Description

Value

false

The hexadecimal value to convert.

Examples

HexToBin(10)

 

HexToDecimal

Description

Converts a hexical value to a decimal value.

Syntax

HexToDecimal( Value )

Returns

Int64

Parameters

Name

Optional

Description

Value

false

The hexadecimal value to convert.

Examples

HexToDecimal(10)

 

HexToOct

Description

Converts a hexical value to a octal value.

Syntax

HexToOct( Value )

Returns

String

Parameters

Name

Optional

Description

Value

false

The hexadecimal value to convert.

Examples

HexToOct(10)

 

Hour

Description

Returns the hour from the date specified.

Syntax

Hour( Date )

Returns

returns integer value of 1 - 24

Parameters

Name

Optional

Description

Date

false

Date to retrieve Hour from

Examples

Hour("12/8/2011"), Hour(varDate), Hour(OrderDateColumn)

 

IfNull

Description

Tests for a null value and, if it exists, returns a default value.

Syntax

IfNull( Value, defaultValue )

Returns

defaultValue

Parameters

Name

Optional

Description

Value

false

The column/variable/string to test for NULL value(s).

defaultValue

false

The value returned when NULL is present

Examples

IfNull(PhoneNumber, 'Unknown'), IfNull(IntRate, @[User::dailyRate])

 

IfNullOrEmpty

Description

Tests for a null or empty value and, if it exists, returns a default value.

Syntax

IfNullOrEmpty( Value, defaultValue )

Returns

defaultValue

Parameters

Name

Optional

Description

Value

false

The column/variable/string to test for NULL or empty value(s).

defaultValue

false

The value returned when NULL is present

Examples

IfNullOrEmpty(PhoneNumber, 'Unknown'), IfNullOrEmpty(IntRate, @[User::dailyRate])

 

IIF

Description

Returns one of two values you specify, based on the results of a condition.

Syntax

IIF( Condition, Value1 , Value2 )

Returns

value1 if the condition is TRUE. value2 if the condition is FALSE.

Parameters

Name

Optional

Description

Condition

false

The condition you want to evaluate. You can enter any valid task editor that evaluates to TRUE or FALSE.

Value1

false

Any datatype except Binary. The value you want to return if the condition is TRUE. The return value is always the datatype specified by this argument. You can enter any valid task editor, including another IIF expression.

Value2

true

Any datatype except Binary. The value you want to return if the condition is FALSE. You can enter any valid task editor, including another IIF expression.

Examples

IIF( SALES > 100, EMP_NAME )

SALES

EMP_NAME

RETURN VALUE

150

John Smith

John Smith

50

Pierre Bleu

'' (empty string)




120

Sally Green

Sally Green




NULL

Greg Jones

'' (empty string)




 

IN

Description

Searches value in the given list. If value is found in the list then it will return True else False. You can perform case-sensetive or case-insensetive compare based on last (optional) argument.

Syntax

IN( Value_To_Find, value1 [, value2, value3..., valueN] [, caseFlag])

Returns

Boolean - True if the value is found, False is the value is not found

Parameters

Name

Optional

Description

Value_To_Find

false

The value you want to search for in the array.

List_Of_Values

false

List of values to be compared.

CaseFlag

true

(Optional) This flag specifies how to compare string data. If this flag is 0 then it will do case-insensetive compare. Anything other than 0 will be case-sensetive compare. By default it will do case-sensetive compare means "DEC" is not same as "Dec"

Examples

In(MONTHNAME("12/12/2013",True), "JAN",FEB","DEC") --This should return False. Last argument not specified means case-sensetive compare. In(MONTHNAME("12/12/2013",True), "JAN",FEB","DEC",0) --This should return True. Last argument=0 means case-insensetive compare. In(MONTHNAME("12/12/2013",True), "JAN",FEB","DEC",1) --This should return False. Last argument=1 means case-insensetive compare.

 

InitCap

Description

Capitalizes the first letter in each word of a string and converts all other letters to lowercase.

Syntax

InitCap( String )

Returns

String. If the data contains multibyte characters, the return value depends on the code page and data movement mode of the Integration Service. NULL if a value passed to the function is NULL.

Parameters

Name

Optional

Description

String

false

Any datatype except Binary. You can enter any valid task editor.

Value1

false

para2 desc.

Examples

InitCap("a fox runs over the river"), InitCap(FirstNameColumn), InitCap(FirstNameColumn + " " + LastNameColumn)

 

InsertChars

Description

Inserts characters into a string at a defined index

Syntax

InsertChars( Base_String, Index, String_To_Insert )

Returns

String. NULL if a value passed to the function is NULL.

Parameters

Name

Optional

Description

Base_String

false

The base string in which characters will be inserted.

Index

false

The index of the Base_String where String_To_Insert will be inserted

String_To_Insert

false

The string to insert at the index.

Examples

InsertChar("400 Dr", 4, "College ") will return "400 College Dr", InsertChar(FullNameColumn, InStr(FullName, " "), "-")

 

InStr

Description

Returns the position of a character set in a string, counting from left to right.

Syntax

INSTR( String_To_Search, Search_Value [, Start] [, Occurrence] [, Comparison_Type ] )

Returns

Integer if the search is successful. Integer represents the position of the first character in the search_value, counting from left to right. 0 if the search is unsuccessful. NULL if a value passed to the function is NULL.

Parameters

Name

Optional

Description

String_To_Search

false

The string must be a character string. Passes the value you want to evaluate. You can enter any valid transformation expression. The results of the expression must be a character string. If not, INSTR converts the value to a string before evaluating it.

Search_Value

false

Any value. The search value is case sensitive. The set of characters you want to search for. The Search_Value must match a part of the string. For example, if you write INSTR('Alfred Pope', 'Alfred Smith') the function returns 0. You can enter any valid transformation expression. If you want to search for a character string, enclose the characters you want to search for in single quotation marks, for example 'abc'.

Start

true

Must be an integer value. The position in the string where you want to start the search. You can enter any valid transformation expression. The default is 1, meaning that INSTR starts the search at the first character in the string. If the start position is 0, INSTR searches from the first character in the string. If the start position is a positive number, INSTR locates the start position by counting from the beginning of the string. If the start position is a negative number, INSTR locates the start position by counting from the end of the string. If you omit this argument, the function uses the default value of 1.

Occurrence

true

A positive integer greater than 0. You can enter any valid transformation expression. If the search value appears more than once in the string, you can specify which occurrence you want to search for. For example, you would enter 2 to search for the second occurrence from the start position. If you omit this argument, the function uses the default value of 1, meaning that INSTR searches for the first occurrence of the search value. If you pass a decimal, the Integration Service rounds it to the nearest integer value. If you pass a negative integer or 0, the dataflow fails.

Comparison_Type

true

The string comparison type, either linguistic or binary, when the Integration Service runs in Unicode mode. When the Integration Service runs in ASCII mode, the comparison type is always binary. Linguistic comparisons take language-specific collation rules into account, while binary comparisons perform bitwise matching. For example, the German sharp s character matches the string "ss" in a linguistic comparison, but not in a binary comparison. Binary comparisons run faster than linguistic comparisons. Must be an integer value, either 0 or 1: - 0: INSTR performs a linguistic string comparison. - 1: INSTR performs a binary string comparison. Default is 0.

Examples

INSTR("Is four in this string", "four"), InStr(ProductDescriptionColumn, "Blue"), InStr(ProductDescriptionColumn, "Blue", 0, 2, 1)

 

IsDate

Description

Determines whether the value is a date.

Syntax

IsDate( Value )

Returns

Boolean

Parameters

Name

Optional

Description

Value

false

The value to check.

Examples

IsDate("7/7/2012")

 

IsEmpty

Description

Determines whether the value is empty.

Syntax

IsEmpty( Value )

Returns

Boolean

Parameters

Name

Optional

Description

Value

false

The value to check.

Examples

IsEmpty(AddressColumn)

 

IsNull

Description

Determines whether the value is null.

Syntax

IsNull( Value )

Returns

Boolean

Parameters

Name

Optional

Description

Value

false

The value to check.

Examples

IsNull(AddressColumn)

 

IsNullOrEmpty

Description

Determines whether the value is null or empty.

Syntax

IsNullOrEmpty( Value )

Returns

Boolean

Parameters

Name

Optional

Description

Value

false

The value to check.

Examples

IsNullOrEmpty(AddressColumn)

 

IsNumber

Description

Determines whether the value is a number.

Syntax

IsNumber( Value )

Returns

Boolean

Parameters

Name

Optional

Description

Value

false

The value to check.

Examples

IsNumber("12345")

 

IsSpaces

Description

Determines whether the value is all spaces.

Syntax

IsSpaces( Value )

Returns

Boolean

Parameters

Name

Optional

Description

Value

false

The value to check.

Examples

IsSpaces("test")

 

Last

Description

Aggregation that returns the last value of the group. Can only be used as part of the Advanced Aggregation component

Syntax

Last( Value [, Condition])

Returns

Object.

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 Last(SalesTotal, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida.

Examples

Last(SalesTotal), Last(SalesTotal, CustomerState="FL")

 

LastDate

Description

Returns the last day of a specified month.

Syntax

LastDate(), LastDate( Date )

Returns

returns date

Parameters

Name

Optional

Description

Date

false

Date to retrieve value from

Examples

LastDate(), LastDate("12/8/2011"), LastDate(varDate), LastDate(OrderDateColumn)

 

LastDateOfPrevMonth

Description

Returns the last day of the previous month.

Syntax

LastDateOfPrevMonth(), LastDateOfPrevMonth( Date )

Returns

returns date

Parameters

Name

Optional

Description

Date

false

Date to retrieve value from

Examples

LastDateOfPrevMonth(), LastDateOfPrevMonth("12/8/2011"), LastDateOfPrevMonth(varDate), LastDateOfPrevMonth(OrderDateColumn)

 

Least

Description

Returns the smallest value from a list of input values. By default, the match is not case sensitive.

Syntax

LEAST( Case_Flag, Value1 [, Value2, ..., ValueN,] )

Returns

value1 if it is the smallest of the input values, value2 if it is the smallest of the input values, and so on. NULL if any of the arguments is null.

Parameters

Name

Optional

Description

CaseFlag

false

Determines whether the arguments in this function are case sensitive. You can enter any valid transformation expression. When CaseFlag is a number other than 0, the function is case sensitive. When CaseFlag is a null value or 0, the function is not case sensitive.

Value

false

Any datatype except Binary. Datatype must be compatible with other values. Value you want to compare against other values. You must enter at least one value argument. If the value is Numeric, and other input values are of other numeric datatypes, all values use the highest precision possible. For example, if some values are of the Integer datatype and others are of the Double datatype, the Integration Service converts the values to Double.

Examples

Least(false, varValues), Least(false, "test1", "test2", "test3")

 

Left

Description

Returns a string containing a specified number of characters from the left side of a string..

Syntax

Left( String, Number_Of_Characters )

Returns

String. NULL if a value passed to the function is NULL.

Parameters

Name

Optional

Description

String

false

String datatype. String expression from which the leftmost characters are returned.

Number_Of_Characters

false

The number of characters to return from the string.

Examples

Left("Stop Here. None of this will be there", 10), Left(FirstNameColumn, 20)

 

Length

Description

Returns the number of characters in a string, including trailing blanks.

Syntax

Length( String )

Returns

Integer representing the length of the string. NULL if a value passed to the function is NULL.

Parameters

Name

Optional

Description

String

false

String datatype. The strings you want to evaluate. You can enter any valid transformation expression.

Examples

Length("How Long Is This?"), Length(FirstNameColumn)

 

Log

Description

Returns the logarithm of a numeric value. Most often, you use this function to analyze scientific data rather than business data.

Syntax

LOG( Base, Exponent )

Returns

Double value. NULL if a value passed to the function is NULL.

Parameters

Name

Optional

Description

Base

false

The base of the logarithm. Must be a positive numeric value other than 0 or 1. Any valid task editor that evaluates to a positive number other than 0 or 1.

Exponent

false

The exponent of the logarithm. Must be a positive numeric value greater than 0. Any valid task editor that evaluates to a positive number greater than 0.

Examples

Log( BaseColumn, ExponentColumn)

 

Log10

Description

Returns the base 10 logarithm of a specified number.

Syntax

Log10( Numeric_Value )

Returns

Numeric

Parameters

Name

Optional

Description

Numeric_Value

false

Passes the values for which you want to the Atan of

Examples

Log10(56)

 

LogBufferRowToFile

Description

Logs a row of data from a dataflow to a flat file.

Syntax

LogBufferRowToFile(File_Path [, Delimeter] [, Quoted_String])

Returns

Nothing

Parameters

Name

Optional

Description

File_Path

false

The path to the file to log the buffer row to.

Delimeter

optional

The file delimeter to be used to seperate the column values. Default is ","

Quoted_String

optional

Tells the engine whether to wrap string values in quotes or not. Default is false.

Examples

LogBufferRowToFile("C:\temp\logfile.ext"), LogBufferRowToFile("c:\temp\logfile.txt", "|", True)]

 

LogError

Description

Logs an error message to the ssis output log (progress tab in BIDS)

Syntax

LogError(Message [, Condition])

Returns

Nothing

Parameters

Name

Optional

Description

Message

false

The message to log.

Condition

true

The condition to evaluate to true before logging the message

Examples

LogError("This is a error"), LogError("Values didn't match", value1!=value2)]

 

LogInfo

Description

Logs an informational message to the ssis output log (progress tab in BIDS)

Syntax

LogInfo(Message [, Condition])

Returns

Nothing

Parameters

Name

Optional

Description

Message

false

The message to log.

Condition

true

The condition to evaluate to true before logging the message

Examples

LogInfo("This is a message"), LogInfo("Values didn't match", value1!=value2)]

 

LogWarning

Description

Logs a warning message to the ssis output log (progress tab in BIDS)

Syntax

LogWarning(Message [, Condition])

Returns

Nothing

Parameters

Name

Optional

Description

Message

false

The message to log.

Condition

true

The condition to evaluate to true before logging the message

Examples

LogWarning("This is a warning"), LogWarning("Values didn't match", value1!=value2)]

 

LookupData

Description

LookupData will use an Advanced Lookup Cache Connection Manager to retrieve data from the cached connection manager. LookupData will return the value of the first output column defined on a cache connection manager. The args parameter array is used to pass in values that match the "Input Parameters" defined on a cache connection manager.

Syntax

LookupData(Cache_Connection_Manager, Arg1 [, Arg2, ..., ArgN])

Returns

returns the value of the output column if a match is found. Returns NULL if no match is found.

Parameters

Name

Optional

Description

Cache_Connection_Manager

false

The Advanced Lookup Cache Connection Manager where the lookup will be performanced. Must use the @@[ConnectionManager] syntax.

Args

false

Args is a parameter array of values that directly correlate to the input parameters defined on a cache connection manager.

Examples

LookupData(@@[ProductCache], ProductID)] - In this example, the cache connection manager (@@[ProductCache]) is storing the products. ProductID is a column that is part of the current component being used (Advanced Derived Column, Advanced Aggregator, etc) and the value of ProductID is passed into LookupData from the Data Flow.
[LookupData(@@[ProductCache], ProductID, OrderDate)] - In this example, the cache connection manager (@@[ProductCache]) is storing the products. ProductID and OrderDate are columns that are part of the current component being used (Advanced Derived Column, Advanced Aggregator, etc) and the value of them is passed into LookupData from the Data Flow.

 

LookupDataByColumn

Description

LookupDataByColumn will use an Advanced Lookup Cache Connection Manager to retrieve data from the cached connection manager and return a specified. Unlike LookupData, which returns the value of the first output column defined on a cache connection manager, LookupDataByColumn can be used to grab a specific column. The args parameter array is used to pass in values that match the "Input Parameters" defined on a cache connection manager.

Syntax

LookupDataByColumn(Cache_Connection_Manager, Column_To_Retrieve, Arg1 [, Arg2, ..., ArgN])

Returns

returns the value of the output column if a match is found. Returns NULL if no match is found.

Parameters

Name

Optional

Description

Cache_Connection_Manager

false

The Advanced Lookup Cache Connection Manager where the lookup will be performanced. Must use the @@[ConnectionManager] syntax.

Column_To_Retrieve

false

The name of an column defined as "Output" on the Advanced Lookup Cache Connection Manager.

Args

false

Args is a parameter array of values that directly correlate to the input parameters defined on a cache connection manager.

Examples

LookupData(@@[ProductCache], "ProductName", ProductID)] - In this example, the cache connection manager (@@[ProductCache]) is storing the products. ProductID is a column that is part of the current component being used (Advanced Derived Column, Advanced Aggregator, etc) and the value of ProductID is passed into LookupData from the Data Flow. "ProductName" is the column being retrieved from the cache connection manager.
[LookupData(@@[ProductCache], "ProductCost", ProductID, OrderDate)] - In this example, the cache connection manager (@@[ProductCache]) is storing the products. ProductID and OrderDate are columns that are part of the current component being used (Advanced Derived Column, Advanced Aggregator, etc) and the value of them is passed into LookupData from the Data Flow. "ProductCost" is the column being retrieved from the cache connection manager.

 

Lower

Description

Converts uppercase string characters to lowercase.

Syntax

LOWER( String )

Returns

Lowercase character string. If the data contains multibyte characters, the return value depends on the code page and data movement mode of the Integration Service. NULL if a value in the selected port is NULL.

Parameters

Name

Optional

Description

String

false

Any string value. The argument passes the string values that you want to return as lowercase. You can enter any valid transformation expression that evaluates to a string.

Examples

Lower("Lower All THIS"), Lower(LastNameColumn)

 

LPad

Description

Adds a set of blanks or characters to the beginning of a string to set the string to a specified length.

Syntax

LPad( First_String, Length [, Second_String] )

Returns

String of the specified length. NULL if a value passed to the function is NULL or if length is a negative number.

Parameters

Name

Optional

Description

First_String

false

Can be a character string. The strings you want to change. You can enter any valid task editor.

Length

false

Must be a positive integer literal. This argument specifies the length you want each string to be.

Second_String

true

Can be any string value. The characters you want to append to the left-side of the first_string values. You can enter any valid task editor. You can enter a specific string literal. However, enclose the characters you want to add to the beginning of the string within single quotation marks, as in 'abc'. This argument is case sensitive. If you omit the second_string, the function pads the beginning of the first string with blanks.

Examples

LPad("PragmaticWorks", 30), LPad(CompanyNameColumn, 50)

 

LTrim

Description

Removes blanks or characters from the beginning of a string.

Syntax

LTRIM( String [, Trim_Set] )

Returns

String. The string values with the specified characters in the trim_set argument removed. NULL if a value passed to the function is NULL. If the trim_set is NULL, the function returns NULL.

Parameters

Name

Optional

Description

String

false

Any string value. Passes the strings you want to modify. You can enter any valid task editor. Use operators to perform comparisons or concatenate strings before removing characters from the beginning of a string.

Trim_Set

true

Any string value. Passes the characters you want to remove from the beginning of the first string. You can enter any valid task editor. You can also enter a character string. However, you must enclose the characters you want to remove from the beginning of the string within single quotation marks, for example, 'abc'. If you omit the second string, the function removes any blanks from the beginning of the string. LTRIM is case sensitive. For example, if you want to remove the 'A' character from the string 'Alfredo', you would enter 'A', not 'a'.

Examples

LTrim(" PragmaticWorks"), LTrim(CompanyNameColumn), LTrim("----PragmaticWorks", "-")

 

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)

 

OctToBin

Description

Converts a octal value to a binary value.

Syntax

OctToBin( Value )

Returns

String

Parameters

Name

Optional

Description

Value

false

The octal value to convert.

Examples

OctToBin(10)

 

OctToDecimal

Description

Converts a octal value to a binary value.

Syntax

OctToDecimal( Value )

Returns

String

Parameters

Name

Optional

Description

Value

false

The octal value to convert.

Examples

OctToDecimal(10)

 

OctToHex

Description

Converts a octal value to a binary value.

Syntax

OctToHex( Value )

Returns

String

Parameters

Name

Optional

Description

Value

false

The octal value to convert.

Examples

OctToHex(10)

 

Power

Description

Returns a specified number to a specified power.

Syntax

Power( Value1, Value2 )

Returns

Numeric

Parameters

Name

Optional

Description

Value1

false

A double-precision floating-point number to be raised to a power.

Value2

false

A double-precision floating-point number that specifies a power.

Examples

Power(10, 3)

 

PV

Description

Returns the present value of an investment, where you make periodic, constant payments and the investment earns a constant interest rate.

Syntax

PV( Rate, Terms, Payment [, Future_Value] [, Type] )

Returns

Numeric.

Parameters

Name

Optional

Description

Rate

false

Numeric. Interest rate earned in each period. Expressed as a decimal number. Divide the percent rate by 100 to express it as a decimal number. Must be greater than or equal to 0.

Terms

false

Numeric. Number of periods or payments. Must be greater than 0.

Payment

false

Numeric. Payment amount due per period. Must be a negative number.

Future_Value

true

Numeric. Future value/balance of the investment. If you omit this argument, PV uses 0.

Type

true

Integer. Timing of the payment. Enter 1 if payment is at the beginning of period. Enter 0 if payment is at the end of period. Default is 0. If you enter a value other than 0 or 1, the Integration Service treats the value as 1.

Examples

PV(Ratevar, Termvar, Paymentvar), PV(3.25, 36, 125.50, 5000, 1)

 

Quarter

Description

Returns the Quarter from the date specified.

Syntax

Quarter( Date )

Returns

returns integer value of 1 - 4

Parameters

Name

Optional

Description

Date

false

Date to retrieve Quarter from

Examples

Quarter("12/8/2011"), Quarter(varDate), Quarter(OrderDateColumn)

 

Rand

Description

Returns a random number between 0 and 1. This is useful for probability scenarios.

Syntax

RAND(), RAND( Seed ), RAND( Min, Max)

Returns

Numeric. For the same seed, the Integration Service generates the same sequence of numbers.

Parameters

Name

Optional

Description

Seed

true

Double. Starting value for the Integration Service to generate the random number. Value must be a constant. If you do not enter a seed, the Integration Service uses the current system time to derive the numbers of seconds since January 1, 1971. It uses this value as the seed.

Min

true

Integer. Minimum value for the generate the random number. Value must be a constant.

Max

true

Integer. Maximum value for the generate the random number. Value must be a constant.

Examples

Rand(), Rand(.5), Rand(1, 10)

 

RegExtract

Description

Extract values out of a string based on a regular expression

Syntax

RegExtract( Input_String, Pattern [, Instance_Of_Pattern_To_Extract] )

Returns

String.

Parameters

Name

Optional

Description

Input_String

false

String value. The string that will be manipulated by the regular expression replace.

Pattern

false

String value. Regular expression pattern.

Instance_Of_Pattern_To_Extract

true

Integer. If more than one instance of the pattern is found in input_string, the value returned will be based on the value provided.

Examples

RegExtract("Get numbers 1234", "[0-9]+"), RegExtract("Get second set of numbers 1234 4567", "[0-9]+", 2)

 

RegMatch

Description

Performs a regular expression match again a string

Syntax

RegMatch( Input_String, Pattern [, Ignore_Case] )

Returns

Boolean. Null if input_string or pattern parameters are null.

Parameters

Name

Optional

Description

Input_String

false

String value. The string that will be searched and matched.

Pattern

false

String value. Regular expression pattern.

Ignore_Case

true

Boolean. Specifies whether the match will be case sensitive.

Examples

RegMatch("123434-", "^[0-9]+$"), RegMatch("Pragmatic Works", "^[a-zA-Z\s]+$")

 

RegReplace

Description

Replace values in a string based on a regular expression

Syntax

RegReplace( Input_String, Pattern, Replacement [, Number_Of_Replacements] )

Returns

String.

Parameters

Name

Optional

Description

Input_String

false

String value. The string that will be manipulated by the regular expression replace.

Pattern

false

String value. Regular expression pattern.

Replacement

false

String value. Replacement value if the pattern is found.

Number_Of_Replacements

true

Integer. The number of times the pattern should be replaced.

Examples

RegReplace("Replace numbers 1234", "[0-9]+", ""), RegReplace("Replace first set of numbers 1234 4567", "[0-9]+", "", 1)

 

RegToken

Description

Splits a string based on a regular expression.

Syntax

RegToken( Input_String, Delimeter_Regex, Occurrence [, Ignore_Case] )

Returns

String. If the input_string or delimeter_regex is NULL, the function returns NULL.

Parameters

Name

Optional

Description

Input_String

false

A string value that will be split by the delimeter_regex parameter.

Delimeter_Regex

false

A regular expression to be used to split the value of Input_String.

Occurrence

false

The occurrence that will be returned after the string has been split.

Ignore_Case

true

Tells the regular expression engine whether or not to perform the split using case sensitivity.

Examples

RegToken("Pragmatic~44~Works~55~Inc", "~[0-9]+~", 2, false)

 

RegTokenCount

Description

Returns the nksn in a string that contains tokens seperated by the regular expression.

Syntax

RegTokenCount( Input_String, Expression_Regex [, Ignore_Case] )

Returns

Integer. If the input_string or expression_regex is NULL, the function returns NULL.

Parameters

Name

Optional

Description

Input_String

false

A string value that will be searched by the expression_regex.

Expression_Regex

false

A regular expression to be located in the Input_String.

Ignore_Case

true

Tells the regular expression engine whether or not to perform the split using case sensitivity.

Examples

RegTokenCount("Pragmatic~44~Works~55~Inc", "~[0-9]+~", false)

 

RemoveChars

Description

Removes a portion of a string starting at an index.

Syntax

RemoveChars( 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 remove characters 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 removing characters. You can enter any valid task editor. If the start position is a positive number, RemoveChars locates the start position by counting from the beginning of the string. If the start position is a negative number, RemoveChars locates the start position by counting from the end of the string. If the start position is 0, RemoveChars searches from the first character in the string.

Length

true

Must be an integer greater than 0. The number of characters you want RemoveChars to return. You can enter any valid task editor. If you omit the length argument, RemoveChars 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

RemoveChars("This will be cut off here. This won't be here", 27), RemoveChars("This will be cut off here. This will be here too.", 27, 4))

 

ReplaceChr

Description

Replaces characters in a string with a single character or no character. REPLACECHR searches the input string for the characters you specify and replaces all occurrences of all characters with the new character you specify.

Syntax

REPLACECHR( CaseFlag, InputString, OldCharSet, NewChar )

Returns

String. Empty string if REPLACECHR removes all characters in InputString. NULL if InputString is NULL. InputString if OldCharSet is NULL or empty.

Parameters

Name

Optional

Description

CaseFlag

false

Must be an integer. Determines whether the arguments in this function are case sensitive. You can enter any valid task editor. When CaseFlag is a number other than 0, the function is case sensitive. When CaseFlag is a null value or 0, the function is not case sensitive.

InputString

false

Must be a character string. Passes the string you want to search. You can enter any valid task editor. If you pass a numeric value, the function converts it to a character string. If InputString is NULL, REPLACECHR returns NULL.

OldCharSet

false

Must be a character string. The characters you want to replace. You can enter one or more characters. You can enter any valid task editor. You can also enter a text literal enclosed within single quotation marks, for example, 'abc'. If you pass a numeric value, the function converts it to a character string. If OldCharSet is NULL or empty, REPLACECHR returns InputString.

NewChar

false

Must be a character string. You can enter one character, an empty string, or NULL. You can enter any valid task editor. If NewChar is NULL or empty, REPLACECHR removes all occurrences of all characters in OldCharSet in InputString. If NewChar contains more than one character, REPLACECHR uses the first character to replace OldCharSet.

Examples

REPLACECHR(false, "I can replace this i with t", "i", "t"), REPLACECHR(True, CompanyNameColumn, "_", " ")

 

ReplaceStr

Description

Replaces characters in a string with a single character, multiple characters, or no character. REPLACESTR searches the input string for all strings you specify and replaces them with the new string you specify.

Syntax

ReplaceStr ( CaseFlag, InputString, OldString1 [, OldString2, ..., OldStringN], NewString )

Returns

String. Empty string if REPLACESTR removes all characters in InputString. NULL if InputString is NULL. InputString if all OldString arguments are NULL or empty.

Parameters

Name

Optional

Description

CaseFlag

false

Must be an integer. Determines whether the arguments in this function are case sensitive. You can enter any valid task editor. When CaseFlag is a number other than 0, the function is case sensitive. When CaseFlag is a null value or 0, the function is not case sensitive.

InputString

false

Must be a character string. Passes the strings you want to search. You can enter any valid task editor. If you pass a numeric value, the function converts it to a character string. If InputString is NULL, REPLACESTR returns NULL.

OldString

false

Must be a character string. The string you want to replace. You must enter at least one OldString argument. You can enter one or more characters per OldString argument. You can enter any valid task editor. You can also enter a text literal enclosed within single quotation marks, for example, 'abc'. If you pass a numeric value, the function converts it to a character string. When REPLACESTR contains multiple OldString arguments, and one or more OldString arguments is NULL or empty, REPLACESTR ignores the OldString argument. When all OldString arguments are NULL or empty, REPLACESTR returns InputString. The function replaces the characters in the OldString arguments in the order they appear in the function. For example, if you enter multiple OldString arguments, the first OldString argument has precedence over the second OldString argument, and the second OldString argument has precedence over the third OldString argument. When REPLACESTR replaces a string, it places the cursor after the replaced characters in InputString before searching for the next match.

NewString

false

Must be a character string. You can enter one character, an empty string, or NULL. You can enter any valid task editor. If NewChar is NULL or empty, REPLACECHR removes all occurrences of all characters in OldCharSet in InputString. If NewChar contains more than one character, REPLACECHR uses the first character to replace OldCharSet.

Examples

REPLACESTR(false, "I can replace this with that", "this", "that"), REPLACESTR(True, CompanyAddressColumn, "Street", "St")

 

Reverse

Description

Reverses the input string.

Syntax

Reverse( String )

Returns

String. Reverse of the input value.

Parameters

Name

Optional

Description

String

false

Any character value. Value you want to reverse.

Examples

Reverse("This will be backwards")

 

Right

Description

Returns a string containing a specified number of characters from the Right side of a string..

Syntax

Right( String, Number_Of_Characters )

Returns

String. NULL if a value passed to the function is NULL.

Parameters

Name

Optional

Description

String

false

String datatype. String expression from which the rightmost characters are returned.

Number_Of_Characters

false

The number of characters to return from the string.

Examples

Right("Only these here", 10), Right(FirstNameColumn, 20)

 

Round(DateTime)

Description

Rounds upto specified part of supplied datetime.

Syntax

Round( Value [, Format])

Returns

Date

Parameters

Name

Optional

Description

Value

false

A date value to be rounded.

Format

true

Date part format which needs to be rounded. See below list for possible format specifier Year = Y, YY, YYY, YYYY Month = M, MM, MON, MONTH Day = D, DD, DDD, DY, DAY Hour = H, HH, HH12, HH24 Minute = MI Second = S,SS Rules: ====== When format=YY and Month >=7 then Year is increased by one and all other parts after year portion is reset When format=MM and Day >=16 then Month is increased by one and all other parts after month portion is reset When format=DD and Hour >=12 then Day is increased by one and all other parts after day portion is reset When format=HH and Minute >=30 then Hour is increased by one and all other parts after hour portion is reset When format=MI and Second >=30 then Minute is increased by one and all other parts after minute portion is reset When format=SS and Milliseconds >=500 then Second is increased by one and all other parts after second portion is reset

Examples

ROUND(TO_DATE('4/16/1998 8:24:19'), 'YY') ==> "1/1/1998" ROUND(TO_DATE('7/16/1998 8:24:19'), 'YY') ==> "1/1/1999" ROUND(TO_DATE('4/15/1998 8:24:19'), 'MM') ==> "4/1/1998" ROUND(TO_DATE('05/22/1998 10:15:29'), 'MM') ==> "6/1/1998" ROUND(TO_DATE('06/13/1998 2:30:45'), 'DD') ==> "6/13/1998" ROUND(TO_DATE('06/13/1998 22:30:45'), 'DD') ==> "6/14/1998" ROUND(TO_DATE('04/01/1998 11:29:35'), 'HH') ==> "04/01/1998 11:00:00" ROUND(TO_DATE('04/01/1998 13:39:00'), 'HH') ==> "04/01/1998 14:00:00" ROUND(TO_DATE('05/22/1998 10:15:29'), 'MI') ==> "05/22/1998 10:15:00" ROUND(TO_DATE('05/22/1998 10:15:30'), 'MI') ==> "05/22/1998 10:16:00" ROUND(TO_DATE('05/22/1998 10:15:29.499'), 'SS') ==> "05/22/1998 10:15:29.000" ROUND(TO_DATE('05/22/1998 10:15:29.500'), 'SS') ==> "05/22/1998 10:15:30.000"

 

Round(Number)

Description

Rounds a decimal value to a specified number of fractional digits.

Syntax

Round( Value [, Decimal_Points] )

Returns

Decimal

Parameters

Name

Optional

Description

Value

false

A decimal number to be rounded.

Decimal_Points

true

The number of decimal places in the return value. If you pass -ve argument then digits before decimal point is truncated. If you ommit this argument then number is rounded to nearest integer value

Examples

ROUND(12.9938,3) ==> 12.994 ROUND(-18.8679, 3) ==> -18.868 ROUND(12.9937, 3) ==> 12.994 ROUND(13252.0, -2) ==> 13200.0 ROUND(1432.99, -2) ==> 1400.00 ROUND(12.99,0.7) ==> 13.0 ROUND(56.34, 0.7) ==> 56.3 ROUND(11.99) ==> 12.0 ROUND(-14.99) ==> -15.0

 

RowNum

Description

Returns the current row number of the buffer from the source.

Syntax

RowNum()

Returns

Numeric (Int64)

Examples

RowNum()

 

Rpad

Description

Converts a string to a specified length by adding blanks or characters to the end of the string.

Syntax

Rpad( First_String, Length [, Second_String] )

Returns

String of the specified length. NULL if a value passed to the function is NULL or if length is a negative number.

Parameters

Name

Optional

Description

First_String

false

Can be a character string. The strings you want to change. You can enter any valid task editor.

Length

false

Must be a positive integer literal. This argument specifies the length you want each string to be.

Second_String

true

Can be any string value. The characters you want to append to the right-side of the first_string values. You can enter any valid task editor. You can enter a specific string literal. However, enclose the characters you want to add to the beginning of the string within single quotation marks, as in 'abc'. This argument is case sensitive. If you omit the second_string, the function pads the beginning of the first string with blanks.

Examples

Rpad("PragmaticWorks", 30), Rpad(CompanyNameColumn, 50)

 

RTrim

Description

Removes blanks or characters from the end of a string.

Syntax

RTRIM( String [, Trim_Set] )

Returns

String. The string values with the specified characters in the trim_set argument removed. NULL if a value passed to the function is NULL.

Parameters

Name

Optional

Description

String

false

Any string value. Passes the strings you want to modify. You can enter any valid task editor. Use operators to perform comparisons or concatenate strings before removing characters from the beginning of a string.

Trim_Set

true

Any string value. Passes the characters you want to remove from the end of the string. You can also enter a text literal. However, you must enclose the characters you want to remove from the end of the string within single quotation marks, for example, 'abc'. If you omit the second string, the function removes blanks from the end of the first string. RTRIM is case sensitive.

Examples

RTrim("PragmaticWorks "), RTrim(CompanyNameColumn), RTrim("PragmaticWorks----", "-")

 

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("Pragm"), 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 />

 

Tan

Description

Returns the tangent of the specified angle.

Syntax

Tan( Numeric_Value )

Returns

Numeric

Parameters

Name

Optional

Description

Numeric_Value

false

Passes the values for which you want to the tangent of

Examples

Tan(55)

 

Tanh

Description

Returns the hyperbolic tangent of the specified angle.

Syntax

Tanh( Numeric_Value )

Returns

Numeric

Parameters

Name

Optional

Description

Numeric_Value

false

Passes the values for which you want to the tangent of

Examples

Tanh(55)

 

ToBigint

Description

Converts a value to a big int (Int64)

Syntax

ToBigint( Value )

Returns

Int64 value

Parameters

Name

Optional

Description

Value

false

The value to convert.

Examples

ToBigint(32), To_Big_Int(89090989)

 

ToChar

Description

Converts a value to a string with optional formatting for date values

Syntax

ToChar( Value [, Format] )

Returns

String value

Parameters

Name

Optional

Description

Value

false

The value to convert.

Format

false

The formatting that will applied when the date value is converted. This is the equivalent of calling DateTime.ToString().

Examples

ToChar(32000), ToChar(Now(), "yyyy/MM/dd")

 

ToDate

Description

Converts a string value to a date with optional formatting

Syntax

ToDate( Value [, Format] )

Returns

String value

Parameters

Name

Optional

Description

Value

false

The value to convert.

Format

false

The formatting that will applied when the date value is converted. This is the equivalent of calling DateTime.ToString().

Examples

ToDate("07/07/2012"), ToDate(Now(), "yyyy/MM/dd")

 

Today

Description

Returns the current date without the time.

Syntax

Today()

Returns

Date

Examples

Today()

 

ToDecimal

Description

Converts a value to a decimal value.

Syntax

ToDecimal( Value [, Scale] )

Returns

Decimal

Parameters

Name

Optional

Description

Value

false

The value to convert.

Scale

false

The scale used to convert the decimal value.

Examples

ToDecimal("1234.78"), ToDecimal("45876.765", 2)

 

ToFloat

Description

Converts a value to a float value.

Syntax

ToFloat( Value )

Returns

Float

Parameters

Name

Optional

Description

Value

false

The value to convert.

Examples

ToFloat("1234.78")

 

ToInteger

Description

Converts a value to an integer value.

Syntax

ToInteger( Value )

Returns

Integer. Any decimal values will be rounded.

Parameters

Name

Optional

Description

Value

false

The value to convert.

Examples

ToInteger("1234.78")

 

Token

Description

Splits a string based on a delimeter.

Syntax

Token( Input_String, Delimeter, occurrence )

Returns

String. If the input_string or delimeter is NULL, the function returns NULL.

Parameters

Name

Optional

Description

Input_String

false

A string value that will be searched.

Delimeter

false

A delimeter to be used to split the value of Input_String.

Occurrence

false

The occurrence that will be returned after the string has been split.

Examples

Token("400 College Dr|Middleburg|FL"|", 2) returns "Middleburg"

 

TokenCount

Description

Gets the number of times a token occurs in a string.

Syntax

TokenCount( Input_String, Token )

Returns

Integer. If the input_string or token is NULL, the function returns NULL.

Parameters

Name

Optional

Description

Input_String

false

A string value that will be searched by the token value.

Token

false

A string expression to be located in Input_String.

Examples

TokenCount("Pragmatic~44~Works~55~Inc", "~")

 

Trim

Description

Removes blanks or characters from the beginning and end of a string.

Syntax

Trim( String [, Trim_Set] )

Returns

String. The string values with the specified characters in the trim_set argument removed. NULL if a value passed to the function is NULL. If the trim_set is NULL, the function returns NULL.

Parameters

Name

Optional

Description

String

false

Any string value. Passes the strings you want to modify. You can enter any valid task editor. Use operators to perform comparisons or concatenate strings before removing characters from the beginning of a string.

Trim_Set

true

Any string value. Passes the characters you want to remove from the beginning of the first string. You can enter any valid task editor. You can also enter a character string. However, you must enclose the characters you want to remove from the beginning of the string within single quotation marks, for example, 'abc'. If you omit the second string, the function removes any blanks from the beginning and end of the string. Trim is case sensitive. For example, if you want to remove the 'A' character from the string 'Alfredo', you would enter 'A', not 'a'.

Examples

Trim(" PragmaticWorks "), Trim(CompanyNameColumn), Trim("----PragmaticWorks", "-")

 

Trunc(Date)

Description

Truncates date to a specific year, month, day, hour, minute, second, millisecond, or microsecond.

Syntax

Trunc( Date [, Format])

Returns

Date.

Parameters

Name

Optional

Description

Date

false

The value to add to the aggregation

Format

true

Anything after this part will be reset to default value. e.g. if you pass HH then anything after Hour (e.g. Minute, Second, Milliseconds) will be reset to 0. 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 /> Microsecond = "US"

value

false

new value for specified date part.

Examples

SetDatePart(Now(),"D", 1)

 

Trunc(Number)

Description

Truncates specified decimal places from numeric value.

Syntax

Trunc( Number [, Precision])

Returns

Number.

Parameters

Name

Optional

Description

Number

false

The number you want to truncate

Precision

true

Number of decimal places to keep. If you pass 0 then all digits after decimal point will be truncated. If you pass -ve precision number then it will truncate digits before decimal point.

Examples

Trunc(567.7789,2) --> 567.7700 Trunc(567.7789) --> 567.0000 Trunc(567.7789,-2) --> 500.0000

 

TryEval

Description

Returns defaultvalue if supplied expression fails to execute. This function help to continue execution on error and it behaves like Try..Catch construct provided in other programming languages such as VB.net/C#.

Syntax

TryEval( expression, defaultvalue [, logerrors] )

Returns

Returns result of main expression if succueed else returns defaultvalue.

Parameters

Name

Optional

Description

expression

false

Any valid expression (without compiler error). E.g. MyCol/0 is valid expression but produce "Divide By Zero" error at runtime.

defaultvalue

false

Any valid expression or value which can be returned when main expression fails.

logerrors

true

Boolean value indicating whether error message should be logged or not. If this argument is True (which is default setting) then error messages will appear in you ssis package execution log as Information icon [e.g. <<EVAL-ERROR at="" ROW=""# 234="">> some error occurred].

Examples

TryEval(TO_CHAR("1")/TO_CHAR("0") , -999 ) -- Returns -999 becuase TO_CHAR("1")/TO_CHAR("0") fails with DivideByZero error TryEval(TO_DATE("55/12/1231","MM/DD/YYYY"), GETDATE() ) -- Returns today's date if date parsing fails

 

Upper

Description

Converts lowercase string characters to uppercase.

Syntax

UPPER( String )

Returns

Uppercase string. If the data contains multibyte characters, the return value depends on the code page and data movement mode of the Integration Service. NULL if a value passed to the function is NULL.

Parameters

Name

Optional

Description

String

false

String datatype. Passes the values you want to change to uppercase text. You can enter any valid task editor.

Examples

Upper("this will be uppercase now"), IIF(Upper(FirstNameColumn) = "CHRIS", 1, 0)

 

WeekOfYear

Description

Returns the week number of the year of the date specified.

Syntax

WeekOfYear( Date )

Returns

returns integer value of 1 - 52

Parameters

Name

Optional

Description

Date

false

Date to retrieve week of year from

Examples

WeekOfYear("12/8/2011"), WeekOfYear(varDate), WeekOfYear(OrderDateColumn)

 

Year

Description

Returns the Year from the date specified.

Syntax

Year( Date )

Returns

returns integer value of year

Parameters

Name

Optional

Description

Date

false

Date to retrieve Year from

Examples

Year("12/8/2011"),Year(varDate), Year(OrderDateColumn)