Task Factory Functions List D-E

Date Compare

DateCompare

DescriptionCompares two dates against each other.
SyntaxDateCompare( Date1, Date2 )
ReturnsInteger. 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
NameOptionalDescription
Date1falseThe first date in the comparison.
Date2falseThe second date in the comparison.
Examples
DateCompare("7/4/2012", "7/5/2012"), DateCompare(varStartDate, varEndDate), DateCompare(OrderDateColumn, StartDateColumn)
DateDiff

DateDiff

DescriptionReturns the difference between two dates against each other.
SyntaxDateDiff( Date1, Date2, Format )
ReturnsInteger. The difference based on datepart between the two days.
Parameters
NameOptionalDescription
Date1falseThe first date in the comparison.
Date2falseThe second date in the comparison.
FormatfalseThe 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

DatePart

DescriptionReturns the part of the date specified in the datepart parameter.
SyntaxDatePart( Date, Date_Part )
ReturnsObject.
Parameters
NameOptionalDescription
DatefalseThe date used to retrieve the datepart from.
Date_PartfalseThe 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

Day

DescriptionReturns the Day from the date specified.
SyntaxDay( Date )
Returnsreturns integer value of 1 - 31
Parameters
NameOptionalDescription
DatefalseDate to retrieve day from
Examples
Day("12/8/2011"), Day(varDate), Day(OrderDateColumn)
Day_Of_Week

Day_Of_Week

DescriptionReturns the Day of the week from the date specified.
SyntaxDay_Of_Week( date )
Returnsreturns integer value of 1 - 7
Parameters
NameOptionalDescription
DatefalseDate to retrieve day of week from
Examples
Day_Of_Week("12/8/2011"), Day_Of_Week(varDate), Day_Of_Week(OrderDateColumn)
DayName

DayName

DescriptionReturns the name of the day (Monday, Tuesday, etc) from the date specified.
SyntaxDayName( Date, Return_Shortname )
Returnsreturns string. Name of the day
Parameters
NameOptionalDescription
DatefalseDate to retrieve day name from
Return_ShortnamefalseReturn the short name of the day (Mon, Tue, Wed)
Examples
DayName("12/8/2011", false), DayName(varDate, True), DayName(OrderDateColumn, false)
DayOfYear

DayOfYear

DescriptionReturns the day of the year of the date specified.
SyntaxDayOfYear( Date )
Returnsreturns integer value of 1 - 366
Parameters
NameOptionalDescription
DatefalseDate to retrieve day of year from
Examples
DayOfYear("12/8/2011"), DayOfYear(varDate), DayOfYear(OrderDateColumn)
DD_

DD_DELETE

DescriptionDD_DELETE is equivalent to the integer literal 2.


DD_INSERT

DescriptionDD_INSERT is equivalent to the integer literal 0.

 

DD_REJECT

DescriptionDD_REJECT is equivalent to the integer literal 3.

 

DD_UPDATE

DescriptionDD_INSERT is equivalent to the integer literal 1.
DecBase64

DecBase64

DescriptionDecodes 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.
SyntaxDecBase64( Value )
ReturnsBinary 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
NameOptionalDescription
ValuefalseString datatype. Data that you want to decode.
Examples
example goes here
 DecimalToBin

DecimalToBin

DescriptionConverts a decimal value to an binary value.
SyntaxDecimalToBin( Value )
ReturnsString
Parameters
NameOptionalDescription
valuefalseThe decimal value to convert.
Examples
DecimalToBin(10)
DecimalToHex

DecimalToHex

DescriptionConverts a decimal value to an hexadecimal value.
SyntaxDecimalToHex( Value )
ReturnsString
Parameters
NameOptionalDescription
ValuefalseThe decimal value to convert.
Examples
DecimalToHex(10)
DecimalToOct

DecimalToOct

DescriptionConverts a decimal value to an octal value.
SyntaxDecimalToHex( Value )
ReturnsInt64
Parameters
NameOptionalDescription
ValuefalseThe decimal value to convert.
Examples
DecimalToOct(10)
Decode

Decode

DescriptionSearches 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.
SyntaxDECODE( Value, First_Search, First_Result [, Second_Search, Second_Result]...[, Default] )
ReturnsFirst_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
NameOptionalDescription
ValuefalseAny datatype except Binary. Passes the values you want to search. You can enter any valid task editor.
SearchfalseAny 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.
ResultfalseAny datatype except Binary. The value you want to return if the search finds a matching value. You can enter any valid task editor.
DefaulttrueAny 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

DeleteFilesOlderThanNDays

DescriptionDeletes from from a directory that are N days old.
SyntaxDeleteFilesOlderThanNDays( Path, Pattern, Older_Than_Days, Recursive [, Continue_On_Failure] )
ReturnsInteger. Returns the number of files deleted
Parameters
NameOptionalDescription
PathfalseThe path where the file will be deleted from.
PatternfalseThe 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_DaysfalseThe number of days that will be used to delete files older than.
RecursivefalseDelete files from subdirectories as well.
Continue_On_FailurefalseContinue deleting files even if one or more files fails to delete.
Examples
DeleteFilesOlderThanNDays("C:\ssis\test", "*.xml", 14, false, true)
DirCreate

DirCreate

DescriptionCreates a directory in the path supplied.
SyntaxDirCreate( Path )
ReturnsBoolean. True if the Directory was created. False if it was not or path parameter is null
Parameters
NameOptionalDescription
PathfalseThe path where the directory will be created.
Examples
DirCreate("C:\ssis\test")
DirDelete

DirDelete

DescriptionRemoves a directory in the path supplied.
SyntaxDirDelete( Path )
ReturnsBoolean. True if the Directory was removed. False if it was not or path parameter is null
Parameters
NameOptionalDescription
PathfalseThe path where the directory will be removed from.
Examples
DirDelete("C:\ssis\test")
DirExists

DirExists

DescriptionCheck if the directory exists in the path supplied.
SyntaxDirExists( Path )
ReturnsBoolean. True if the Directory exists. False if it does not or path parameter is null
Parameters
NameOptionalDescription
PathfalseThe path where the directory is located.
Examples
DirExists("C:\ssis\test")
DirGetCreationTime

DirGetCreationTime

DescriptionReturn the creation time of the directory.
SyntaxDirGetCreationTime( Path )
ReturnsDateTime. If the path does not exist, the minimum date for your system is returned.
Parameters
NameOptionalDescription
PathfalseThe path where the directory is located.
Examples
DirGetCreationTime("C:\ssis\")
DirGetLastAccessTime

DirGetLastAccessTime

DescriptionReturn the time the directory was last accessed.
SyntaxDirGetLastAccessTime( Path )
ReturnsDateTime. If the path does not exist, the minimum date for your system is returned.
Parameters
NameOptionalDescription
PathfalseThe path where the directory is located.
Examples
DirGetLastAccessTime("C:\ssis\")
DirGetLastWriteName

DirGetLastWriteTime

DescriptionReturn the time the directory was last written to.
SyntaxDirGetLastWriteTime( Path )
ReturnsDateTime. If the path does not exist, the minimum date for your system is returned.
Parameters
NameOptionalDescription
PathfalseThe path where the directory is located.
Examples
DirGetLastWriteTime("C:\ssis\")
 DirGetRoot

DirGetRoot

DescriptionReturns the root directory of the path specified.
SyntaxDirGetRoot( Path )
ReturnsString. Null if the path parameter is Null
Parameters
NameOptionalDescription
PathfalseThe path where the directory is located.
Examples
DirGetRoot("C:\ssis\test")
DirMove

DirMove

DescriptionMoves a directory in from one path to another.
SyntaxDirMove( Path_Source, Path_Destination )
ReturnsBoolean. True if the Directory was moved. False if it was not or either the Path_Source or Path_Destination parameters are null
Parameters
NameOptionalDescription
Path_SourcefalseThe path where the directory will be moved from.
Path_DestinationfalseThe path where the directory will be moved to.
Examples
DirMove("C:\ssis\test", "C:\ssis\test1")
 DirSetCreationTime

DirSetCreationTime

DescriptionSet the time the directory was created.
SyntaxDirSetCreationTime( Path, Date_Created )
ReturnsBoolean. True if the value is set. False if either parameter is null.
Parameters
NameOptionalDescription
PathfalseThe path where the directory is located.
Date_CreatedfalseThe date the directory was created.
Examples
DirSetCreationTime("C:\ssis\", "7/6/2012 14:24")
 DirSetLastAccessTime

DirSetLastAccessTime

DescriptionSet the time the directory was last accessed.
SyntaxDirSetLastAccessTime( Path, Date_Last_Access )
ReturnsBoolean. True if the value is set. False if either parameter is null.
Parameters
NameOptionalDescription
PathfalseThe path where the directory is located.
Date_Last_AccessfalseThe date the directory was last accessed.
Examples
DirSetLastAccessTime("C:\ssis\", "7/6/2012 14:24")
 DirSetLastWriteTime

DirSetLastWriteTime

DescriptionSet the time the directory was written to accessed.
SyntaxDirSetLastWriteTime( Path, Date_Last_Written )
ReturnsBoolean. True if the value is set. False if either parameter is null.
Parameters
NameOptionalDescription
PathfalseThe path where the directory is located.
Date_Last_WrittenfalseThe date the directory was last written to.
Examples
DirSetLastWriteTime("C:\ssis\", "7/6/2012 14:24")
 EncBase64

EncBase64

DescriptionEncodes 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.
SyntaxEncBase64( Value )
ReturnsEncoded value. NULL if the input is a null value.
Parameters
NameOptionalDescription
ValuefalseBinary or String datatype. Data that you want to encode.
Examples
example goes here
 EndsWith

EndsWith

DescriptionEndsWith determines whether a string ends with a character or string value
SyntaxEndsWith ( String_To_Search, Search_Value )
ReturnsBoolean. True if string_to_search ends with search_value. NULL if a value passed to the function is NULL.
Parameters
NameOptionalDescription
String_To_SearchfalseCharacter string. The string to search.
Search_ValuefalseCharacter string. The value to find at the end of String_To_Search
Examples
EndsWith("400 College Dr", "Dr"), If EndsWith(AddressColumn, "Dr") Then ...)
Error

Error

DescriptionCauses the Integration Service to skip a row and issue an error message, which you define. The error message displays in the progress log.
SyntaxERROR( String )
ReturnsString.
Parameters
NameOptionalDescription
StringfalseString 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

ExecuteSQL

DescriptionExecutes 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)
SyntaxExecuteSQL( connection_manager, sql_statement [, is_stored_procedure] [, command_timeout] [, column_name_or_index] [, param1, param2 ... paramN])
ReturnsReturns 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
NameOptionalDescription
Connection_ManagerfalseThe 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_StatementfalseString value used representing the Stored Procedure that will be executed.
Is_Stored_ProceduretrueBoolean 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_TimeouttrueTimeout in seconds for sql command. 0 means unlimited (Default=0)
Column_Name_OR_IndextrueIf 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
ParameterstrueList 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

Exp

DescriptionReturns 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.
SyntaxEXP( Exponent )
ReturnsDouble value. NULL if a value passed as an argument to the function is NULL.
Parameters
NameOptionalDescription
ExponentfalseNumeric 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 )