SSIS – How to get the last date of a month?

I need to get the first and last date of a month in SSIS. These will be used to process partitions in my SSAS Cube afterwards.

The riddle answer is: The last day of the month is the day before the first day of next month.

1) Create 3 variables: Date, MonthBeginning and MonthEnding

2) Change MonthBeginning property “evaluate as expression to true” and edit the expression as follow
(DT_WSTR,4)YEAR(@[User::Date])  + “/” + (DT_WSTR,4)MONTH(@[User::Date]) + “/01”
3) Change MonthEnding property “evaluate as expression to true” and edit the expression as follow:
(DT_WSTR,4)YEAR(DATEADD(“dd”,-1,DATEADD(“mm”,1,(DT_DATE) @[User::MonthBeginning]))) + “/” + (DT_WSTR,4)MONTH(DATEADD(“dd”,-1,DATEADD(“mm”,1,(DT_DATE) @[User::MonthBeginning]))) + “/” + (DT_WSTR,4)DAY(DATEADD(“dd”,-1,DATEADD(“mm”,1,(DT_DATE) @[User::MonthBeginning])))

I like my dates in the yyyy/mm/dd format hence the big expression above otherwise it returns something looking like this: mm/dd/yyyy. I hate this.

yyyy/mm/dd format makes it easier for me since I just need to replace / with “” (empty string) to get a nice integer 20091001 and 20091031 which will represent the foreign key in my fact table 🙂 yay.

Then you can go along and use this to partition your cube and so on.

To summarize: we get the first day of the month we seek, say 2009/10/01. We add one month: 2009/11/01 and we remove one day: 2009/10/31. Bingo. Last date of the month.