SQL SSIS Derived Column formula/syntax issues

1
votes

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.

2
sql-serverssis
Alternatively, if SSIS is too limited I could execute a TSQL SP that looks at the staging table and processes the above logic... I would then add in my SSIS ETL a step to execute the SP. – user1709091
Are you trying to say that if the sales month and date (formatted like 1225) is greater than today's date (0304) then set dYtdYr = 0 else set dYtdYr = sales Year? Would that work? – William Salzman
The T-SQL MONTH and DAY functions both return integers. Did you really want to add those two numbers together, or is this really an attempt to compare two dates? – Edmund Schweppe

2 Answers

2
votes

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.

0
votes

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