5/25/2018

Month Name and Day Name in SSIS expression


Month Name and Day Name in SSIS expression:

 

In many scenarios we need to evaluate month name or days name from date in SSIS expression, for an example, we have to pick file from a shared location where folder name is based on current month so in that case we need month name as per date to access that folder with month name as:

C:\abc\Files\2018\May\Monday\ABC.txt

So here you can see we have to access folder May and then if today is Monday then need to pick file from Monday folder so for this we can create a variable in SSIS package and use below expression to define path as:

"C:\\abc\\File\\ " + (DT_WSTR, 4) YEAR( Getdate()) +"\\"+

(MONTH(getdate()) == 1 ? "01. Jan" : MONTH(getdate()) == 2 ? "02. Feb" : MONTH(getdate()) == 3 ? "03. Mar" :

 

  MONTH(getdate()) == 4 ? "04. Apr" : MONTH(getdate()) == 5 ? "05. May" : MONTH(getdate()) == 6 ? "06. June" :

 

  MONTH(getdate()) == 7 ? "07. July" : MONTH(getdate()) == 8 ? "08. Aug" : MONTH(getdate()) == 9 ? "09. Sep" :

 

  MONTH(getdate()) == 10 ? "10. Oct" : MONTH(getdate()) == 11 ? "11. Nov" : MONTH(getdate()) == 12? "12. Dec":"")

 

+"\\"+ (DATEPART("dw",GETDATE())==1? "Sunday": DATEPART("dw",GETDATE())==2?"Monday":

 

DATEPART("dw",GETDATE())==3?"Tuesday": DATEPART("dw",GETDATE())==4?"Wednesday":

 

DATEPART("dw",GETDATE())==5?"Thursday": DATEPART("dw",GETDATE())==6?"Friday":

 

DATEPART("dw",GETDATE())==7?"Saturday":"" )

 

+"\\ABC.txt "

 
Here in above code, you can see I have used two functions to find Month name and Day name to create path.

Below are expressions:

Expressions for Month Name:

(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":"")


Expressions for Day Name:

DATEPART("dw",GETDATE())==1? "Sunday":

DATEPART("dw",GETDATE())==2?"Monday":

DATEPART("dw",GETDATE())==3?"Tuesday":

DATEPART("dw",GETDATE())==4?"Wednesday":

DATEPART("dw",GETDATE())==5?"Thursday":

DATEPART("dw",GETDATE())==6?"Friday":

DATEPART("dw",GETDATE())==7?"Saturday":""