Instr Function or CharIndex function in SSIS!!!

• Stad

  SSC Eights!

  Points: 964

  More actions

  July 5, 2011 at 2:54 pm

  #254002

  Hi ,

  I would like to use same functionality as CharIndex function in Sql Server or Instr function in Oracle using SSIS.

  Ex -

  SELECT CASE

  WHEN CHARINDEX('abc', [tblA].col1) > 0 THEN [tblA].col1

  ELSE ''

  END

  FROM tblA

  Please suggest me to get this in SSIS.

  Thanks!

  Stad

• TangoVictor

  SSChampion

  Points: 10976

  More actions

  July 5, 2011 at 3:24 pm

  #1348615

  Use a derived column.

  You can either add it as a new column or choose the Replace 'ColumnName' option

  Give it a formula like:

  FINDSTRING("abc", [MyInputColumn],1 ) > 0 ? [MyInputColumn] : ""

  the "?" indicates an IF, the first value after the ? is the true branch and after the ":" is the false branch.

  Formula language isn't pretty but that would give you what you need.

  You could also code this in VB.net or C#.net with a script component setup as a transformation.

  I almost like that better and would definitely use it if the logic gets complicated, nested IF's in formula language are a bear.

  Additional resource:

  http://msdn.microsoft.com/en-us/library/ms141671.aspx

• Stad

  SSC Eights!

  Points: 964

  More actions

  July 6, 2011 at 7:25 am

  #1348943

  Thank You Tom!

  It worked for me.

  Stad