CODEWITHSUNDEEP
sql-serverssis
user1709091
user1709091

Reputation:

SQL SSIS Derived Column formula/syntax issues

I have a formula that I need to be able to put into my SSIS Derived Column... Below it is..

if MONTH(Sls.SlsDt) + DAY(Sls.SlsDt) > MONTH(TODAY) + DAY(TODAY) then
    dYdtYr = 0.
  else
    dYdtYr = YEAR(Sls.SlsDt).

How would I put the above business logic into a SQL SSIS ETL Derived Column Transformation?

Is Derived Column the right option to accomplish this?

The ETL package is an import from CSV to SQL staging table, during this import I need to output a new column (or replace existing) that has the logic above implemented.

The input rows contain the MONTH and DAY and dYdtYr columns.. In a sense, I would need to override the dYdtYr value with new value based on the above logic.

Upvotes: 1

Views: 863

Answers (2)

William Salzman
William Salzman

Reputation: 6446

Derived column set as replace column have it replace dYtdYr. Put the following into the expression:

(MONTH(Sls.SlsDt) * 100 + DAY(Sls.SlsDT) > MONTH(getdate())*100 + DAY(getdate())) ? 0 : YEAR(Sls.SlsDt)

This takes the month value (03 for march) multiplies it by 100 to get 300 and adds it to the day value (04 for today) and yeilds a value like 0304. This will work so that any day of the year before or equal to this value will be less than that number and any day of the year after today will be greater. Note this does not take into account years. This means that December 31 from 3 years ago would get a 0 in dYtdYr but March 3 from 3 years ago would (today) get the year for SlsDt for it. I would guess that if this is your desired outcome you are trying to build a YTD Year over Year comparison.

Upvotes: 2

Matt Busche
Matt Busche

Reputation: 14333

If you can use TSQL then you can use a CASE statement

CASE 
 WHEN MONTH(Sls.SlsDt) + DAY(Sls.SlsDt) > MONTH(getDate()) + DAY(getDate()) 
 THEN dYdtYr = 0
ELSE
 dYdtYr = YEAR(Sls.SlsDt)
END AS ColumnName

Upvotes: 0

Related Questions