-1

I have the following as sql

SQL TO SSIS derived column month name and 1ST of month

DATENAME(MM,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) --month name

DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) --date 1st of month

cant get these to work as derived columns in SSIS

how do I convert please team

  • 1
    What version of SQL Server/SSIS are you running? – MguerraTorres Jan 28 '19 at 14:11
  • 2010 version please team – pete Jan 28 '19 at 14:32
  • 2
    Are those in your SQL query or in you Derived column? It looks like you're using the SQL syntax and not what SSIS uses. Also 2010 isn't a valid version of SQL Server. That may be the visual studio client you're using. – Zane Jan 28 '19 at 14:38
2

Based on your comment about going back 3 months, I decided to take a different approach.

Create the following SSIS Variables below (I will describe the expression to be assigned to each)

enter image description here

DateIncrementDecrement will contain the number of months to add or subtract from the current date (3 for plus 3 months and negative 3 (-3) for minus 3 months). Altering the value of this variable with successive runs should give you the correct results.

AdjustedDate expression is

DATEADD("MONTH", @[User::DateIncrementDecrement], getdate())

FirstOfMonth expression is

(DT_WSTR,10)(DT_DBDATE)(DATEADD("DAY",-(DAY(@[AdjustedDate])) + 1,@[AdjustedDate]))

MonthName expression is

(DT_WSTR,15)(MONTH(@[User::AdjustedDate]) == 1 ? "January" : MONTH(@[User::AdjustedDate]) == 2 ? "February" : MONTH(@[User::AdjustedDate]) == 3 ? "March" : MONTH(@[User::AdjustedDate]) == 4 ? "April" : MONTH(@[User::AdjustedDate]) == 5 ? "May" : MONTH(@[User::AdjustedDate]) == 6 ? "June" : MONTH(@[User::AdjustedDate]) == 7 ? "July" : MONTH(@[User::AdjustedDate]) == 8 ? "August" : MONTH(@[User::AdjustedDate]) == 9 ? "September" : MONTH(@[User::AdjustedDate]) == 10 ? "October" : MONTH(@[User::AdjustedDate]) == 11 ? "November" : MONTH(@[User::AdjustedDate]) == 12 ? "December" : "")

In your Derived Column Transformation Editor, create two new derived columns

enter image description here

MonthName expression is

(DT_WSTR,15)@[User::MonthName]

FirstOfMonth expression is

@[User::FirstOfMonth]

| improve this answer | |
  • hi, this is great thank you. What happens if I need to please go back 3 months? got to be honest , hence why I was using the DATEADD(mm part. e.g so date is 01/10/2018, and month is October – pete Jan 28 '19 at 16:03
  • @pete - you're welcome :) – Scott Hodgin Jan 29 '19 at 12:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.