Post reply

ssis expreesion

  • 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

  • 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

    WHERE type = '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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply