All geek questions in one place

Checking the number in DerivedColumn data flow task in SSIS

Is there a way where I can check if a field is numeric in a derived column / Conditional separation of a data flow task in Sql Server Integration Services 2005.

Functionality should be like an ISNUMERIC () function.

I am currently using

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

in the displayed column expression to check if the field name is numeric. But if the field name contains characters like 123ABC instead of the digit 123, the above expression throws an exception due to conversion failure and termination of the package.

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

+3
source share
2 answers

We use the following technique:

  • In the property form for the derived column component, you can click the Configure Error Output button to invoke the error output configuration.

  • Set the error and truncate actions for this column (or any other column) as Ignore Failure

  • Now, if the conversion of this column is not performed for the row, the package will continue to run. The column value for this row will be null. You can use the following component to check for null values ​​and take appropriate action.

+4
source

. script.

0

Source: https://habr.com/ru/post/1702377/

More articles:


All Articles