|
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
|
