Saturday 9 April 2011

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

4 comments:

  1. great article. thank you for sharing.

    ReplyDelete
  2. If using SQL source, then just add another column in your source SQL that does ISNUMERIC check providing flag for your dataflow. That would be a lot simpler than any of above solutions (and faster than the script solution) albeit it relies on having SQL based source and it pushes logic to the SQL side of things which takes it out of your ETL code.

    ReplyDelete
    Replies
    1. If your source is a database then TSQL is probably easier: https://msdn.microsoft.com/nl-nl/library/ms186272.aspx

      Delete
  3. Very good point, thanx much for this column.
    Ondrej

    ReplyDelete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...