At some point or another, you’ve probably run into the road block that is the lack of an ISNUMERIC() equivalent within the SSIS expression language. While you can’t use ISNUMERIC() in an SSIS transform, such as a Conditional Split Transform or a Derived Column Transform, that doesn’t mean you can’t check to see if a field is numeric using the SSIS expression language (If you feel so inclined, you can use a Script Task to check if a field is numeric. Tim Murphy, another Pragmatic Works consultant, covers that in his blog here).

As I said before, there is a way we can use a Derived Column Transform (or Conditional Split) to check if a field is numeric. After dragging in a Derived Column Transform into your Data Flow Task, create a new column to be added as a new column to your data flow. Give it a meaningful name and use this expression:

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

Then near the bottom of the Derived Column Transform Editor window, click Configure Error Output. You need to tell SSIS to Ignore failure on Error, as seen here:

Optionally, you could choose to redirect rows that are not numeric to the Error output of the Derived Column and then handle those rows there.

Now when we run the Data Flow Task, we should see results like this:

You can see that the rows that are not numeric have a NULL value for the IsNumeric field we created with the Derived Column. Rows that are numeric have a 1. This way it is easy for us to determine which rows of a certain field are numeric and which are not numeric by checking for NULLs in our field called IsNumeric.