Search code examples
ssisderived-column

Derived Column Expression Doesn't work but no error is given

I am trying to write a formula that will give me the Base Transaction Amount (Int) based on the Reporting Period (varchar)

So if reporting period, for example, is 201803 the expression should evaluate if that period is bigger than last year same month 201701 or smaller than last year next month 201702 or if it's between 01 and 02 of this year and so on.

I came up with the following formula that doesn't give me an error, but when I run the package I end up with 0 in the derived columns when it should be the Base Transaction Amount.

PrevYear01 column: (ReportingPeriod >= "Year(getdate()-1)" + "01") && (ReportingPeriod < "Year(getdate()-1)" + "02") ? BaseTransactionAmount : 0

ThisYear01 column: (ReportingPeriod >= "Year(getdate())" + "01") && (ReportingPeriod < "Year(getdate())" + "02") ? BaseTransactionAmount : 0

Please Help!

Solution

  • You may need to cast the calculated year before concatenating:

    (DT_WSTR,4)(year(getdate())-1) +"01"