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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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?
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())
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

cheryl9063Author Commented:
Getting the attached error error
vdr1620Commented:
Add double Quotes .. "01" instead of 01, "02" instead of 02... etc
cheryl9063Author Commented:
I did that and no error but all it displays is October.. It does not display
ME_OTW_Received for Sept-11
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
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
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
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())
cheryl9063Author Commented:
It just returns September..

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

Jason YousefSr. 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

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
cheryl9063Author Commented:
Thanks!! Stayed with expression builder but will try other way next time.. Sorry took so long to reward points..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.