Nov 24, 2009

Expressions

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:
  1. Precedence constraint - Should evaluate to TRUE or FALSE
  2. Setting up variable value - Should be of the variable data type
  3. For Loop
  4. Setting up properties for various objects dynamically especially Connection Strings for various Connection Managers
  5. Derived Column Transform(most often used here)
  6. 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())
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 function:
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" 1
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

3 comments:

  1. Hi Sudeep,
    Most of us are confused while using the expression. This post is really very informative and helpful.

    Thanks... Really great effort.

    ReplyDelete
  2. Hi Sudeep,

    It's really helpfull with all the functions at one place. I am trying to get month name in my expression and found your blog. In the FindString function you mentioned the result would be "ab" but the FindString will only return the integer.
    ref: http://msdn.microsoft.com/en-us/library/ms141748.aspx

    I am looking for a similar function that you defined for FindString.

    Thanks

    ReplyDelete
  3. Thanks Anonymous for pointing that out. It was a mistake on my part have corrected that :)

    ReplyDelete

Thanks for your valuable comment!!