-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

closed as unclear what you're asking by mustaccio, Max Vernon, Colin 't Hart, MDCCL, Michael Green Jan 29 at 1:42

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    What version of SQL Server/SSIS are you running? – MguerraTorres Jan 28 at 14:11
  • 2010 version please team – pete Jan 28 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 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]

  • 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 at 16:03
  • @pete - you're welcome :) – Scott Hodgin Jan 29 at 12:24

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