July 5, 2011 at 2:54 pm
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
July 5, 2011 at 3:24 pm
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:
July 6, 2011 at 7:25 am
Thank You Tom!
It worked for me.
Stad
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply