Issue with SSIS variable expression using If THEN

  • i have a vairable in SSIS package, i want to implement if then condition in that variable expression based on file name

    i will get full file name form the package - [User::FileName] has values LK0011_20200218094659_FulArc_CODE01_PRODATA

    LK0011_20200218094659_FulArc_CODE02_PRODATA

    LK0011_20200218094659_FulArc_CODE03_PRODATA

    I will get these file names form for each loop container. we will have 3 different files. we have to differentiate with CODE01/02 and 03

    IF (SUBSTRING(@[User::FileName],30,6) == "CODE01" ) THEN 'A'

    IF (SUBSTRING(@[User::FileName],30,6) == "CODE02" ) THEN 'B'

    IF (SUBSTRING(@[User::FileName],30,6) == "CODE03" ) THEN 'C'

     

     

    I am getting error, expression is not well formatted or invalid token

  • In SSIS expression language, there is no IF() function. You'll need to use the conditional operator, which looks like this:

    (someCondition) ? valueIfTrue : valueIfFalse

    This correlates to the IF/THEN/ELSE logic as such:

    IF: someCondition

    THEN: valueIfTrue

    ELSE: valueIfFalse

  • Take a look at the TOKEN function (https://docs.microsoft.com/en-us/sql/integration-services/expressions/token-ssis-expression?view=sql-server-ver15) - it will be much easier using that instead of substring.

    Build a variable (name it FileCode) as: TOKEN(@[User::FileName], "_", 3)

    Then your expression becomes:

    (@[User::FileCode] = "CODE01") ? "A" : (@[User::FileCode] = "CODE02") ? "B" : (@[User::FileCode] = "CODE03") ? "C" : "Z"

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

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

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