SSIS Lookup Wild Card in Ref Table and Insert the Ref Table column in the output


  • Hi,

    I have a scenario where I have a source table with ID, Desc, Type columns. I have a reference table RefCd, RefDesc

    Source Table with Ex Data

    ID Desc Type

    1 add nos Math

    2 can sub nos Math

    3 split two wrds Eng

    Reference Table with Ex Data

    RefCd RefDesc

    AD add

    SU sub

    SL split

    With the data above, I would want a resultset something like below

    ID Desc           Type  RefCd

    1 add two nos  Math  AD

    2 can sub nos   Math     SU

    3 split two wrds  Eng  SL

    Basically, my Lookup will be on the Desc col in source to %RefDesc% in ref table i.e., something Desc like '%RefDesc%'.  

    After lookup, I would want the RefCd column to be in the output cols list.

    Please help!!

    Friday, July 12, 2013 12:54 AM