[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to fix my ssis date expression

Posted on 2011-10-03
15
Medium Priority
?
395 Views
Last Modified: 2013-11-18
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())
0
Comment
Question by:cheryl9063
15 Comments
 
LVL 16

Expert Comment

by:vdr1620
ID: 36904034
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
 
LVL 1

Author Comment

by:cheryl9063
ID: 36904066
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 36904157
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 1

Author Comment

by:cheryl9063
ID: 36904197
Getting the attached error error
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 36904337
Add double Quotes .. "01" instead of 01, "02" instead of 02... etc
0
 
LVL 1

Author Comment

by:cheryl9063
ID: 36904374
I did that and no error but all it displays is October.. It does not display
ME_OTW_Received for Sept-11
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 36904408
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
 
LVL 1

Author Comment

by:cheryl9063
ID: 36904440
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 36904676
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
 
LVL 1

Author Comment

by:cheryl9063
ID: 36905849
"" + "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
 
LVL 1

Author Comment

by:cheryl9063
ID: 36905853
It just returns September..

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

0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36906846
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
 
LVL 16

Accepted Solution

by:
vdr1620 earned 1600 total points
ID: 36907655
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
 
LVL 1

Assisted Solution

by:MRugs
MRugs earned 400 total points
ID: 36913447
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
 
LVL 1

Author Closing Comment

by:cheryl9063
ID: 37020386
Thanks!! Stayed with expression builder but will try other way next time.. Sorry took so long to reward points..
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question