SSIS - how to add a new column : the year of last month

• onlo

  SSCertifiable

  Points: 5080

  More actions

  January 12, 2011 at 9:50 pm

  #230042

  My source text file :

  Cost Profit

  3000 1500

  4000 2800

  Today is year 2011-01, my destination SQL table should be :

  Year Month Cost Profit

  2010 12 3000 1500

  2010 12 4000 2800

  I used Derived Column but failed :

  Derived Column Name : year

  Derived Column : <add as new column>

  Expression : SUBSTRING( CONVERT(VARCHAR, DATEADD(MONTH,-1,GETDATE()), 101), 7, 4 )

  Result : failed, very disappointed as this SUBSTRING works in SQL Query Analyzer, why it didn't work in SSIS.

  Any ideal ? Thanks.

• Koen Verbeeck

  SSC Guru

  Points: 259115

  More actions

  January 13, 2011 at 3:15 am

  #1272894

  The expression language used in the derived column is not the same as using TSQL.

  When in the derived column editor, look at the predefined functions at the top right.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• January 13, 2011 at 5:51 am

  This was removed by the editor as SPAM