Issue with SSIS variable expression using If THEN

• mcfarlandparkway

  SSCertifiable

  Points: 7699

  More actions

  April 10, 2020 at 2:48 pm

  #3742004

  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

• Tim Mitchell

  SSCoach

  Points: 15681

  More actions

  April 10, 2020 at 3:06 pm

  #3742010

  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

  Tim Mitchell, Microsoft Data Platform MVP
  Data Warehouse and ETL Consultant
  TimMitchell.net | @Tim_Mitchell | Tyleris.com
  ETL Best Practices

• Jeffrey Williams

  SSC Guru

  Points: 90252

  More actions

  April 10, 2020 at 8:55 pm

  #3742116

  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