How to fix my ssis date expression

In my expression builder for an email attachment in SSIS the code below returns
ME_OTW_Received for 10-2011

How can I get it to return
ME_OTW_Received for Sept-11

"" + "ME_OTW_Received"+" for "  + RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",0,GETDATE())),2) + "-" + (DT_WSTR,4)YEAR(GETDATE())
LVL 1
cheryl9063Asked:
Who is Participating?
 
vdr1620Commented:
OK Try this


" ME_OTW_Received for "  + (DT_WSTR,15)(
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "01" ? "January" :
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "02" ? "February" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "03" ? "March" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "04" ? "April" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "05" ? "May" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "06" ? "June" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "07" ? "July" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "08" ? "August" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "09" ? "September" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "10" ? "October" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "11" ? "November" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "12" ? "December" : "InvalidMonth") + "-" + (DT_WSTR,4)(YEAR(GETDATE()))

Open in new window

0
 
vdr1620Commented:
SSIS does not have an in built function like SQL Server to get the Month Name .. So you will have to create a variable and write an If Else expression (Link below) to choose SEPT when its 10 ..

http://www.sqlis.com/post/Expression-Date-Functions.aspx
0
 
cheryl9063Author Commented:
How do you write.. I have been staring at examples and cant get them to work.. I need this

ME_OTW_Received for Sept-11

I need the current month - 28 days ever time.. How do I write this?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
vdr1620Commented:
Try this.. if you want currentMonth -28 days then you will need to write an Expression to get the Month of the (Currentmonth - 28 days) and use it as below

RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) .. this will give you the Month Number before the 28 days.. Use this in the below Exp by removing RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",0,GETDATE())),2)



"" + "ME_OTW_Received"+" for "  + RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",0,GETDATE())),2) == 01 ? "January" : RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",0,GETDATE())),2) == 02 ? "February" : RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",0,GETDATE())),2) == 03 ? "March" :
  RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",0,GETDATE())),2) == 04 ? "April" : RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",0,GETDATE())),2) == 05 ? "May" : RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",0,GETDATE())),2) == 06 ? "June" :
  RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",0,GETDATE())),2) == 07 ? "July" : RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",0,GETDATE())),2) == 08 ? "August" : RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",0,GETDATE())),2) == 09 ? "September" :
  RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",0,GETDATE())),2) == 10 ? "October" : RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",0,GETDATE())),2) == 11 ? "November" :
  RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",0,GETDATE())),2) == 12 ? "December" : "InvalidMonth" + "-" + (DT_WSTR,4)YEAR(GETDATE())
0
 
cheryl9063Author Commented:
Getting the attached error error
0
 
vdr1620Commented:
Add double Quotes .. "01" instead of 01, "02" instead of 02... etc
0
 
cheryl9063Author Commented:
I did that and no error but all it displays is October.. It does not display
ME_OTW_Received for Sept-11
0
 
vdr1620Commented:
Please read my above post ... I suggested you to use RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) instead of current Expression to get the month before last 28 days .... RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",0,GETDATE())),2)


you will just have to replace -28 instead of 0 in the DateAdd function
0
 
cheryl9063Author Commented:
I am, I know how to get September to show but that is ALL that is displaying..

I'm getting
September
 instead of
ME_OTW_Received for September-11
0
 
vdr1620Commented:
Did you by mistake delete "" + "ME_OTW_Received"+" for "  from your expression ... if not please post your expression and I will take a look at it
0
 
cheryl9063Author Commented:
"" + "ME_OTW_Received"+" for "  + RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "01" ? "January" : RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "02" ? "February" : RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "03" ? "March" :
  RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "04" ? "April" : RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "05" ? "May" : RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "06" ? "June" :
  RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "07" ? "July" : RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",0,GETDATE())),2) == "08" ? "August" : RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "09" ? "September" :
  RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "10" ? "October" : RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "11" ? "November" :
  RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "12" ? "December" : "InvalidMonth" + "-" + (DT_WSTR,4)YEAR(GETDATE())
0
 
cheryl9063Author Commented:
It just returns September..

Not sure why the "ME_OTW_Received"+" for "  + and the year will not show with it..

0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Do you need SEPT  or SEPTEMPER ???

check the attched..
"ME_OTW_Received  for "  + (RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "01" ? "January" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "02" ? "February" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "03" ? "March" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "04" ? "April" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "05" ? "May" :
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "06" ? "June" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "07" ? "July" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",0,GETDATE())),2) == "08" ? "August" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "09" ? "September" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "10" ? "October" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "11" ? "November" : 
RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",-28,GETDATE())),2) == "12" ? "December" : "" )   + "-" + (DT_WSTR,4)YEAR(GETDATE())

Open in new window

0
 
MRugsCommented:
just a quick question: does this need to be done with the expression builder syntax? Why not create a package variable that is populated by an execute SQL task that uses T-SQL DATENAME, DATEADD functions to accomplish this? you would only need to refer to the variable in the expression for the email task instead of creating a monster of an expression. I applaud the dedication to staying in the expression builder, but it has some obvious limitations.
0
 
cheryl9063Author Commented:
Thanks!! Stayed with expression builder but will try other way next time.. Sorry took so long to reward points..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.