Check numeric in DerivedColumn Data Flow Task in SSIS

Question

Is there any way where I can check if the field is numeric in derived column/Conditional Split of the data Flow task in Sql Server Integration Services 2005.

Functionality should be like ISNUMERIC() function.

Currently I'm using

((DT_NUMERIC,12,0)fieldname= (DT_NUMERIC,12,0)fieldname)

in the derived column expression to check if the field name is Numeric. But if the field name contains characters like 123ABC instead of numeral 123, the above expression throws an exception because of conversion failure and package execution stops.

If we had a function like ISNUMERIC(), than the we need not do all this circus. Does anybody know if there is any function like this to check if the field is numeric or not in SSIS.

Answer 1

You can redirect rows on error and handle the error. Or use a script task.

Answer 2

We use the following technique:

1. On the property form for the derived column component you can click the button marked 'Configure Error Output' to bring up the error output configuration.

2. Configure the error and truncation actions for this column (or any other column) to be "Ignore Failure"

3. Now, If the conversion of this column fails for a row, the package will continue to execute. The column value for that row will be set to null. You can use a subsequent component to check for null values, and take appropriate action.