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.
Expressions can be used at various places:
Set File name in the following format: FileName_MMMDD_YYYY.txt (FileName_Nov23_2009.txt)
Functions available to create EXPRESSIONS
I will talk about some of the most commonly used functions in the next section.
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
Get today’s date in the format DD/MM/YYYY
1.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
1.(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)
or
1.RIGHT((“0000000000”+“STRING”),10)1.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.
Create file name with the current time stamp in the format: FileName_YY-MM-DD-HHMMSS.txt
1.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
1."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
1.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.
1.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
1.FINDSTRING("111abcda","aa",1) >0 ? Step1 :
Step2Set File name in the following format: FileName_MMMDD_YYYY.txt (FileName_Nov23_2009.txt)
1."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"
|
|
&&
|
Logical
AND
|
|
||
|
Logical
OR
|
No comments:
Post a Comment