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