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\ DataFile20180525.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\20180524.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)
"19991011 20:34:52.123 3:30" !=
(DT_DBDATE)"19991012"

The
system converts the expression,(DT_DBDATE)"19991012", to
DT_DBTIMESTAMPOFFSET. The example evaluates to TRUE because the converted
expression becomes "19991012 00:00:00.000 +00:00", which is not
equal to the value of the other expression,(DT_DBTIMESTAMPOFFSET,3)
"19991011 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
threecharacter string to doublebyte 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)
"19991011"

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) "19991011 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) "19991011 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())==1DAY((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
email 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, userdefined variables, and the
concatenation (+) operator. The userdefined 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

No comments:
Post a Comment