Search code examples
sqlsql-servervisual-studiossis

Check if column value is Numeric. SSIS

I have a column with datatype of varchar. I would like to replace all the values that are not numeric with NULL.

So for example my column can contain a value of MIGB_MGW but also 1352. The current expression I am using with Derived Column Transformation Editor is:

(DT_I4)kbup == (DT_I4)kbup ? 1  : 0

But of course this replaces all the values I want to keep with 1. What expression would I use to keep the numeric values? (1352 in this example)

Solution

  • If you want a null of varchar type, you can use NULL(DT_STR). For a DT_I4 you can use NULL(DT_I4) etc.

    You can then use (DT_I4)kbup in place of your 1 to return the original varchar value that you want to keep, converted to a DT_I4:

    (DT_I4)kbup == (DT_I4)kbup ? (DT_I4)kbup : NULL(DT_I4)