Hi,
Lets talks about EXPRESSIONS.
It is one of the most interesting and used feature available in SSIS. At times we tend to over do in out attempt to work using Expressions. One needs to decide when it is best to use an expression. A few pointers that I would like to provide.
- Use expressions where they are small
- Do not make the expression overtly complicated. It makes management difficult.
- Avoid too many nested conditions in expressions.
Expressions can be used at various places:
- Precedence constraint - Should evaluate to TRUE or FALSE
- Setting up variable value - Should be of the variable data type
- For Loop
- Setting up properties for various objects dynamically especially Connection Strings for various Connection Managers
- Derived Column Transform(most often used here)
- Conditional Split
Sample expressions:
Get today’s date in the format DD/MM/YYYY
RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()),2) + "/" + RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",31,GETDATE())),2) + "/" + (DT_WSTR,4)YEAR(GETDATE())
Convert 8.8.80 or 08.8.80 or 08.08.80 to 08/08/1980
(DT_DBDate)(RIGHT("0"+SUBSTRING([DateColumn],1, FINDSTRING([DateColumn],".", 1)-1),2) + "/" + RIGHT("0"+SUBSTRING([DateColumn],FINDSTRING( [DateColumn],".", 1)+1, FINDSTRING( [DateColumn],".", 2)-FINDSTRING( [DateColumn],".", 1)-1) ,2) + "/"+"19"+RIGHT([DateColumn], 2))
Pad a string with zeroes on the left(output length-10)
RIGHT((“0000000000”+“STRING”),10)
or
REPLICATE("0",10 - LEN("String")) + "STRING"
Convert date of input string column to date time. Validate that if its value is NULL or empty or illegal date it should be converted to Null date.
ISNULL([Column 0]) || LEN(TRIM((DT_WSTR,10)[Column 0])) == 0 || [Column 0]==”00-00-0000” NULL(DT_DATE) : (DT_DBDATE)((DT_WSTR,10)[Column 0])
Create file name with the current time stamp in the format: FileName_YY-MM-DD-HHMMSS.txt
"FileName_" + SUBSTRING((DT_WSTR,30)GETDATE(),1,10) + SUBSTRING((DT_WSTR,30)GETDATE(),12,2) SUBSTRING((DT_WSTR,30)GETDATE(),15,2) + SUBSTRING((DT_WSTR,30)GETDATE(),18,2) + ".txt"
Get the difference of the date in a column w.r.t. today’s date
DATEDIFF("d",(DT_DATE)(SUBSTRING([Column 0],10,2) + "-" + SUBSTRING([Column 0],12,2) + "-" + SUBSTRING([Column 0],6,4)),GETDATE())
If length of a column or variable is zero set its value to “asd” else set it to the variable value.
LEN([Column 0]) == 0 ? "asd" : [Column 0]
FINDSTRING("111abcda","aa",1) this will give 0
FINDSTRING("111abcda","a",1) this will give 4
FINDSTRING("111abcda","a",2) this will give 8
Check if a string contains a value do Step1 else Step2
FINDSTRING("111abcda","aa",1) >0 ? Step1 : Step2
Set File name in the following format: FileName_MMMDD_YYYY.txt (FileName_Nov23_2009.txt)
"FileName_" + (MONTH(GETDATE()) == 1 ? "Jan" : MONTH(GETDATE()) == 2 ? "Feb" : MONTH(GETDATE()) == 3 ? "Mar" : MONTH(GETDATE()) == 4 ? "Apr" : MONTH(GETDATE()) == 5 ? "May" : MONTH(GETDATE()) == 6 ? "Jun" : MONTH(GETDATE()) == 7 ? "Jul" : MONTH(GETDATE()) == 8 ? "Aug" : MONTH(GETDATE()) == 9 ? "Sep" : MONTH(GETDATE()) == 10 ? "Oct" : MONTH(GETDATE()) == 11 ? "Nov" : MONTH(GETDATE()) == 12 ? "Dec" : "InvalidMonth") + (DT_WSTR,3)DAY(GETDATE()) + "_" + (DT_WSTR,5)YEAR(GETDATE()) + ".txt"
Functions available to create EXPRESSIONS
I will talk about some of the most commonly used functions in the next section.
|
Mathematical Functions
|
|
FUNCTION
|
Result
|
DataType
|
|
ROUND( 2.23 , 2 )
|
2
|
Integer
|
|
ROUND( 2.53 , 2 )
|
3
|
Integer
|
|
CEILING(2.23 )
|
3
|
Numeric
|
|
FLOOR(2.9)
|
2
|
Numeric
|
|
ABS(3.2)
|
3
|
Numeric
|
|
ABS(3.9)
|
4
|
Numeric
|
|
ABS(-3.2)
|
3
|
Numeric
|
|
ABS(-3.9)
|
4
|
Numeric
|
|
String Functions
|
|
FUNCTION
|
Result
|
DataType
|
|
LOWER( "ABD" )
|
abc
|
String
|
|
UPPER( "abd" )
|
ABD
|
String
|
|
LTRIM( " abc " )
|
"abc "
|
String
|
|
RTRIM( " abc " )
|
" abc"
|
String
|
|
TRIM( " abc " )
|
"abc"
|
String
|
|
REVERSE( "abc" )
|
"cba"
|
String
|
|
RIGHT( abc, 2 )
|
"bc"
|
String
|
|
SUBSTRING( "abcde", 2 , 3 )
|
"bcd"
|
String
|
|
REPLICATE("a",3)
|
"aaa"
|
String
|
|
FINDSTRING("abcd","ab",1)
|
"ab"
|
String
|
|
REPLACE("Apple", "pp", "n app")
|
"An apple"
|
String
|
|
Date/Time Functions
|
|
FUNCTION
|
Result
|
DataType
|
|
DATEADD( "mm", 1, (DT_DATE)"12/24/2009" )
|
1/24/2010 0:00
|
DateTime
|
|
DATEADD( "dd", -1, (DT_DATE)"12/24/2009" )
|
12/23/2009 0:00
|
DateTime
|
|
DATEADD( "yy", 1, (DT_DATE)"12/24/2009" )
|
12/24/2010 0:00
|
DateTime
|
|
DATEDIFF( "dd", (DT_DATE)"12/24/2009", DT_DATE)"01/26/2010" )
|
33
|
Integer
|
|
DATEDIFF( "mm", (DT_DATE)"12/24/2009", (DT_DATE)"01/26/2010" )
|
1
|
Integer
|
|
DATEDIFF( "yy", (DT_DATE)"12/24/2009", (DT_DATE)"01/26/2010" )
|
1
|
Integer
|
|
DATEPART( "dd", (DT_DATE)"12/24/2009" )
|
24
|
Integer
|
|
DATEPART( "mm", (DT_DATE)"12/24/2009" )
|
12
|
Integer
|
|
DATEPART( "yy", (DT_DATE)"12/24/2009" )
|
2009
|
Integer
|
|
DAY( (DT_DATE)"12/24/2009" )
|
24
|
Integer
|
|
MONTH( (DT_DATE)"12/24/2009" )
|
12
|
Integer
|
|
YEAR( (DT_DATE)"12/24/2009" )
|
2009
|
Integer
|
|
NULL Functions
|
|
FUNCTION
|
Result
|
DataType
|
|
ISNULL( «expression» )
|
TRUE/FALSE
|
Boolean
|
|
NULL(DT_DATE)
|
NULL
|
DateTime
|
|
Type Casts
|
|
FUNCTION
|
|
(DT_STR, «length», «code_page»)
|
|
(DT_WSTR, «length»)
|
|
(DT_NUMERIC, «precision», «scale»)
|
|
(DT_DECIMAL, «scale»)
|
|
Operators
|
|
FUNCTION
|
Comments
|
|
?:
|
If then Else -> "Condition"?"True Action"
: "False Action"
|
|
&&
|
Logical AND
|
|
||
|
Logical OR
|