SSIS - How To Use Expression Task To Get Day Name and Month Name In SSIS Package

Expression Task is added in SQL Server Integration Services 2012. The task can be used to set the values of variables. In previous version of SSIS we were writing expressions on Variables itself or we were using Script Task to set the values of Variables.

Let's say we do need Month Name and Day Name in our SSIS Package later to create archive files with Day Name or Month Name, or we need to Send in Email or simply we have to load this information with data to a table.
SSIS does not provide Month Name or Day Name built in functions. I have a post for SSIS 2008, where I created variables and wrote expressions on them to get Month Name and Day Name, Click Here .

In our this demo, we will be using Expression Tasks to populate the Values of DayName and MonthName variables at start of SSIS Packages.

Step 1: 

Let's create an SSIS Package with two Variables DayName and MonthName as shown below.
Fig 1: Create variable DayName and MonthName in SSIS Pacakge

Step 2: 
Bring Expression Tasks to the Control Flow and change the name of it to "Set DayName Variable". Double Click on it and write expression as shown below
Fig 2: Set DayName variable value by using Expression

Here is the expression I have used for Fig 2.

@[User::DayName]=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":""


Bring 2nd expression Task and rename it to "Set MonthName Variable" and open it by double clicking.
Fig 3: Write Expression to set MonthName variable value in Expression Task

Here is the expression that I used for Fig 3.

@[User::MonthName]=(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":"")

Step 3: 
Bring Sequence Container and connect both Expression Tasks to it. Add BreakPoint to it so we can see the values of variables. I have a post how to use BreakPoint if you want to learn about BreakPoint. Click Here

Fig 4: Values of DayName and MonthName variables are set by Expression Tasks.

As we can see that the values are set for both variables by using Expression Tasks. I was looking for something if we could set the value of multiple variables in one Expression Task but looks like that is not possible at this point. 
If we want to keep the SSIS with less number of Tasks, we still would like to use script task in case where we are setting the values of more than two variables. 



6 comments:

  1. Thank you for this,I have added the 'MonthName' to my expression and this causes my Package to hang.The expression works but seems to be battling.

    ReplyDelete
  2. I feel SSIS and other aspects really help one know more about the most useful aspect to solve complex IT problems.

    SSIS Postgresql Write

    ReplyDelete
  3. Reviewing custom essay writing services Reviews is an excellent way to choose the best service for you. Third-party reviews are often helpful and enlightening. Also, if you are looking for the best essay writing service, you should make sure that it offers a guarantee. This may be a money-back guarantee or a satisfaction guarantee, but making sure you have this guarantee before you place an order is vital for protecting your money and time.

    ReplyDelete
  4. Wr1ter is one of high-quality essay writing service usa link . They have a strict policy of using only high-quality sources for their papers, and they always ensure that all their writers abide by academic standards. Thus, you can be sure that your essays will be written with the utmost care and attention to detail.

    ReplyDelete
  5. I know everything about a good job and digital sphere. For this reason I decided that it will be a really good solution in order to try finding the best place in remote work which is a special website in order to find a perfect way about how to work as a freelancer and to perform it really effective.

    ReplyDelete