Pages

Sunday, November 30, 2014

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/