Useful SSIS Expressions

Search Expressions

• If you want to find a specific text (e.g. 'Value') in a word ( underValue1), you could use following expression:
      FINDSTRING(col, "value", 1) > 0
• If you want to find a specific text (e.g. 'Value') at the beginning of a word (Value1), you could use following expression:
     FINDSTRING(col, "value", 1) == 1
• If you want to find a specific text (e.g. 'value') at the end of a word (underValue), you could use following expression:
     REVERSE(LEFT(REVERSE(col), X)) == "Value"

Assign NULL values (tested in version 2012 )

    (DT_STR, 4, 1252)NULL(DT_STR, 4, 1252)

Character replace expressionsUse hex notations to remove special characters in a fields of a Data Conversion transformation.

• TRIM(REPLACE(ColumnName,"\x0009","")) - Remove horizontal
• TRIM(REPLACE(ColumnName,"\x000D","")) - Remove carriage
• TRIM(REPLACE(ColumnName,"\x000A","")) - Remove line feeds
• TRIM(REPLACE(ColumnName,"\x00A0","")) - Remove Non breaking space (HTML tag - &nbsp)
  
  Hex codes added in SSIS expression should have 6 characters that should start with "\x" and end with 4 characters. Therefore we might need to add extra 0s. (e.g to replace "&" we should use REPLACE(ColumnName,"\x0026","") )
  
  Following websites can be used to find the hex code for known characters.
  http://www.ascii-code.com/