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

Breakpoint does not work within SSIS Script Component

Case
I cannot debug (use breakpoints) in a Script Component. What's wrong?

Solution
The Script Component does not support the use of breakpoints. Therefore, you cannot step through your code and examine values as the package runs. There are a few workarounds to still get some form of debugging.

* Script Task not debugging? Switch Project Properties to 32bit for SSIS 2005 and 2008! *
1) MessageBox
The good old messagebox is a simple quick way of displaying some value. But it could be a little annoying with a lot of records.

// C# Code
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    System.Windows.Forms.MessageBox.Show("SomeMessage: " + Row.YourColumn);
}

Messagebox.Show

2) Fire events
You can fire events and watch the execution result tab.

// C# Code
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    bool fireAgain = true;
    this.ComponentMetaData.FireInformation(0, "ScriptComponent", "SomeMessage: " + Row.YourColumn, string.Empty, 0, ref fireAgain);
}

Partial Execution Results

3) Trace log
The .Net framework has it's own trace features which you can use the write messages to a listner. There are a lot of listners (third party, open source or your own custom handmade .net application), but your can also download one from Microsoft.com: DebugView for Windows.

// C# Code
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    System.Diagnostics.Trace.WriteLine("SomeMessage: " + Row.YourColumn);
}

Let me know if you have an other workaround. And also see/vote for this Feedback request on Microsoft.com.
* Update 17 November: Debugging has been added in SQL Server 2012 RC0 *