ssis expreesion

• siddaiah.k

  SSC Enthusiast

  Points: 191

  More actions

  July 7, 2012 at 3:44 am

  #147167

  Hi,

  I have 1 t0 100 integer numbers on a column,how to write expression to convert integer numbers in to week numbers i mean 1 to 7 same numbers and 8 to 1,9 to 2 .please tell me expression and which trandformation i have to use.plz tell me step by step

  id(input) output

  1 1

  2 2

  3 3

  4 4

  5 5

  6 6

  7 7

  8 1

  9 2

  . .

  . .

  .

  100

  thanks for advance

• Koen Verbeeck

  SSC Guru

  Points: 259105

  More actions

  July 9, 2012 at 3:43 am

  #1510230

  In TSQL it's fairly easy:

  WITH CTE_Tally AS

  (

  SELECT TOP 100 number FROM MASTER.dbo.spt_values

  WHEREtype= 'P'

  AND number> 0

  )

  SELECT

  number

  ,week_day = CASE WHEN (number % 7) = 0

  THEN 7

  ELSE (number % 7)

  END

  FROM CTE_Tally

  ORDER BY number

  You can mimick this in a derived column by using the build-in SSIS functions.

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