Derived Column Editor in SSIS

• MTY-1082557

  SSCarpal Tunnel

  Points: 4540

  More actions

  February 11, 2010 at 1:38 pm

  #220559

  I load a Flat File in my SSIS, before to insert the data into my Sql Table, I do some transformation in some fields to clean the data.

  I have one column that I need to replace some characters into blanks ““. I’m using a Derived Column Transformation Editor to do this.

  This is the Example data:

  NameSeqTime

  "Peter MB2"1 17:53

  "Lulu BM11B"1 17:53

  "Oscar BM9"1 18:23

  "Nick BM2A"1 18:33

  The Clean data should be inserted like:

  NameSeqTime

  Peter1 17:53

  Lulu1 17:53

  Oscar1 18:23

  Nick 1 18:33

  The codes (MB2, BM11B, BM9 and BM2A) will always appear in my original flat File and I will need to clean them all the time. I tried this and it works for one character.

  REPLACE(Name,"BM9"," ")

  I couldn’t find the way to do it for the rest of the codes in one line on the Expression.

  I did this and it failed.

  REPLACE(Name,"BM9"," ") || REPLACE(Name,"BM2A"," ")

  Any idea?

• MTY-1082557

  SSCarpal Tunnel

  Points: 4540

  More actions

  February 11, 2010 at 2:03 pm

  #1117768

  I GOT IT,

  YOU NEED TO DO THIS:

  LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(Name,"BM2",""),"BM2A",""),"BM9",""),"BM11B","")))

  Hope it can helps someone 🙂