Mostly used Expression in SSIS

SSIS Mostly used expressions:

Here are few examples of expressions for SSIS.

Example	Description
"C:\\Project\\DataFile" + (DT_WSTR, 30)(DT_DBDATE)GETDATE() + ".csv"	This will provide a File path with current date in file name as C:\Project\ DataFile2018-05-25.csv
ROUND(Amount, 2)	Round of two decimal places for a given value/column
"C:\\XYZ\\Files\\" + (DT_WSTR,4)YEAR(DATEADD("dd", -1, GETDATE())) + "-"     + RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("dd", -1, GETDATE())), 2) + "-"     + RIGHT("0" + (DT_WSTR,2)DAY(DATEADD("dd", -1, GETDATE())), 2) + ".txt"	An expression, but this time deriving the file name based on yesterday’s date, useful for loading the previous day's data: C:\XYZ\Files\2018-05-24.txt
ISNULL(ColumnName)||TRIM(ColumnName)==""?"Unknown": ColumnName	In this example, the statement determines that if the ColumnName is blank or NULL, it will be set to unknown. To make a Logical AND condition, use "&&" instead of the "||" operator.
(DT_DBTIMESTAMPOFFSET,3) "1999-10-11 20:34:52.123 -3:30" != (DT_DBDATE)"1999-10-12"	The system converts the expression,(DT_DBDATE)"1999-10-12", to DT_DBTIMESTAMPOFFSET. The example evaluates to TRUE because the converted expression becomes "1999-10-12 00:00:00.000 +00:00", which is not equal to the value of the other expression,(DT_DBTIMESTAMPOFFSET,3) "1999-10-11 20:34:52.123 -3:30".
(DT_I4) 3.57	Casts a numeric value to an integer.
(DT_STR,1,1252)5	Casts an integer to a character string using the 1252 code page.
(DT_WSTR,3)"Cat"	Casts a three-character string to double-byte characters.
(DT_DECIMAl,2)500	Casts an integer to a decimal with a scale of two.
(DT_NUMERIC,7,3)4000	Casts an integer to a numeric with a precision of seven and scale of three.
(DT_STR,50,1252)FirstName	Casts values in the FirstName column, defined with an nvarchar data type and a length of 50, to a character string using the 1252 code page.
(DT_BOOL)"True"	Casts the string literal "True" to a Boolean.
(DT_DBDATE) "1999-10-11"	Casts a string literal to DT_DBDATE.
(DT_DBTIME2, 5) "16:34:52.12345"	Casts a string literal to the DT_DBTIME2 data type that uses 5 digits for fractional seconds. (The DT_DBTIME2 data type can have between 0 and 7 digits specified for fractional seconds.)
(DT_DBTIMESTAMP2, 4) "1999-10-11 16:34:52.1234"	Casts a string literal to the DT_DBTIMESTAMP2 data type that uses 4 digits for fractional seconds. (The DT_DBTIMESTAMP2 data type can have between 0 and 7 digits specified for fractional seconds.)
(DT_DBTIMESTAMPOFFSET, 7) "1999-10-11 16:34:52.1234567 + 5:35"	Casts a string literal to the DT_DBTIMESTAMPOFFSET data type that uses 7 digits for fractional seconds. (The DT_DBTIMESTAMPOFFSET data typecan have between 0 and 7 digits specified for fractional seconds.)
DATEADD("Month", 1,GETDATE())	Adds one month to the current date.
DATEADD("day", 21, ModifiedDate)	Adds 21 days to the dates in theModifiedDate column.
DATEADD("yyyy", 2, (DT_DBTIMESTAMP)"8/6/2003")	Adds 2 years to a literal date.
DATEDIFF("dd", (DT_DBTIMESTAMP)"8/1/2003", (DT_DBTIMESTAMP)"8/8/2003")	Calculates the number of days between two date literals. If the date is in "mm/dd/yyyy" format, the function returns 7.
DATEDIFF("mm", (DT_DBTIMESTAMP)"8/1/2003",GETDATE())	Returns the number of months between a date literal and the current date.
DATEDIFF("Week", ModifiedDate,@YearEndDate)	Returns the number of weeks between the date in theModifiedDate column and theYearEndDate variable. IfYearEndDate has a date data type, no explicit casting is required.
DATEPART("month", (DT_DBTIMESTAMP)"11/04/2002")	Returns the integer that represents the month in a date literal. If the date is in mm/dd/yyyy" format, this example returns 11.
DATEPART("dd", ModifiedDate)	Returns the integer that represents the day in theModifiedDate column.
DATEPART("yy",GETDATE())	Returns the integer that represents the year of the current date.
DAY((DT_DBTIMESTAMP)GETDATE())==1||DAY((DT_DBTIMESTAMP)GETDATE())==15?1:2	Can be used to set the LoggingMode property of a package. The expression uses the DAY and GETDATE functions to get an integer that represents the day datepart of a date. If the day is the 1st or 15th, logging is enabled; otherwise, logging is disabled. The value 1 is the integer equivalent of theLoggingMode enumerator member Enabled, and the value 2 is the integer equivalent of the member Disabled. You must use the numeric value instead of the enumerator member name in the expression.
"PExpression-->Package: (" + @[System::PackageName] + ") Started:" + (DT_WSTR, 30) @[System::StartTime] + " Duration:" + (DT_WSTR,10) (DATEDIFF( "ss", @[System::StartTime] , GETDATE() )) + " seconds"	Can be used to set the Subjectproperty of a Send Mail task and provide a useful e-mail subject. The expression uses a combination of string literals, system variables, the concatenation (+) and cast operators, and the DATEDIFF and GETDATE functions. The system variables are the PackageNameand StartTime variables.
"Rows Processed: " + "\n" +" NASDAQ: " + (dt_wstr,9)@[nasdaqrawrows] + "\n" + " NYSE: " + (dt_wstr,9)@[nyserawrows] + "\n" + " Amex: " + (dt_wstr,9)@[amexrawrows]	Can be used to set theMessageSource property of a Send Mail task. The expression uses a combination of string literals, user-defined variables, and the concatenation (+) operator. The user-defined variables are namednasdaqrawrows,nyserawrows, andamexrawrows. The string "\n" indicates a carriage return.
DATEPART("weekday", GETDATE()) ==2?"notepad.exe":"mspaint.exe"	Can be used to set theExecutable property of an Execute Process task. The expression uses a combination of string literals, operators, and functions. The expression uses the DATEPART and GETDATE functions and the conditional operator.
(DATEPART("DW",GETDATE()) == 7) ? "SUNDAY" : (DATEPART("DW",GETDATE()) == 1) ? "MONDAY" : (DATEPART("DW",GETDATE()) == 2) ? "TUESDAY" : (DATEPART("DW",GETDATE()) == 3) ? "WEDNESDAY" : (DATEPART("DW",GETDATE()) == 4) ? "THURSDAY" : (DATEPART("DW",GETDATE()) == 5) ? "FRIDAY" : (DATEPART("DW",GETDATE()) == 6) ? "SATURDAY" : "NULL"	Weekday name