IsNumeric or IsNumber expression in SSIS

Case
How do I check if a column value contains a number? I can't find the SSIS equivalent of the .net methods IsNumeric or IsNumber.

Solution
That expression isn't available in SSIS. See/vote for this Feedback request on Microsoft.com.
There are basicly two different workarounds available: (A) Script Component or (B) try casting the value to an int. A third option is the (C) FINDSTRING expression, but that works only single positions.

Solution A
Script Component with a .Net method to check whether a value is a number.

1) Script Component
Add a Script Component (type: transformation) where you need to do the check.

Script Component Transformation

2) Select Input Columns
Add the column that needs to be checked as a ReadOnly input column.

Input Columns Tab

3) Add Output Column
Add a new column to the Output columns on the tab Inputs and Outputs. The type shoot be Boolean and give it a suitable name.

Inputs and Outputs Tab

4) The Script
The easiest way is to use Visual Basic.net as the language because vb.net has a method named IsNumeric and C# doesn't.

' VB.Net code

' Check whether the string value contains a number

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper



 _

 _

Public Class ScriptMain

    Inherits UserComponent



    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        ' Standard VB.net method, indicating whether

        ' an expression can be evaluated as a number

        If (IsNumeric(Row.TextNumbers)) Then

            Row.IsNumeric = True

        Else

            Row.IsNumeric = False

        End If

    End Sub



End Class

And the C# example

// C# Code

// Check whether the string value contains a number

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;



[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

    public override void Input0_ProcessInputRow(Input0Buffer Row)

    {

        if (IsNumeric(Row.TextNumbers))

        {

            Row.IsNumeric = true;

        }

        else

        {

            Row.IsNumeric = false;

        }

    }



    // Custom C# method, indicating whether an 

    // expression can be evaluated as a number

    static bool IsNumeric(string myString)

    {

        try

        {

            Int32.Parse(myString);

        }

        catch

        {

            return false;

        }

        return true;

    } 

}

More C# equivalent methods for the IsNumeric are available here.

5) The Result
I added an empty Derived Column with a Data Viewer for testing.

The Result

Note: you could also add a second output port and create a conditional split construction with a Script Component. An example of two outputs can be found here.


Solution B
Try to cast a string to an integer. If it works, it's an integer and if it raises an error, it isn't. The casting can be done by a Derived Column Transformation or a Data Conversion Transformation.

1) Add Derived Column
Add a Derived Column where you need to do the check.

Derived Column

2) Add Expression
Add a new column with the following expression and give it a suitable name: !ISNULL((DT_I8)TextNumbers). All numbers will result in True and all non-numbers will raise an error.

3) Ignore error
Go to the Configure Error Output window in the Derived column and ignore errors for the new field.

Ignore error

4) The Result
I added an empty Derived Column with a Data Viewer for testing. Notice the NULL value for non-numbers. That's the difference between the two methods. You can add an ISNULL expression in the next Derived column to replace the null values with false.

The Result

5) Alternative with Data Conversion
An alternative could be to try convert the value to an int via a Data Conversion Transformation and also ignore any errors. Than add a Derived Column with an expression to check for null values: !ISNULL(IntTextNumbers)

Same Result

The results of methods A and B are equal. Choose the method that fits you.

Solution C
If you want to check one position for a numeric value, you could also use an FINDSTRING expression:
FINDSTRING("0123456789", SUBSTRING([YourColumn], 1, 1), 1) != 0