Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Checking for IsNumeric()

Many ex-DTS users are miffed that the SSIS expression language does not have an equivalent of the VB Script IsNumeric() function that is used so extensively in DTS and to be honest, its a fair shout.

The following code is very typical in DTS packages:

If IsNumeric(RetailPrice) = False Then

    DTSDestination("RetailPrice") = Empty

Else

    DTSDestination("RetailPrice") = DTSSource("Col010")

End If 

 

As I said above, the SSIS expression language does not have an IsNumeric() function so the Derived Column component cannot be used to do the same. Script Component to the rescue!!! The following code inside a script component will achieve the same.

 

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)

        Dim retNum As Double

 

        'use TryParse to test if the value is numeric or not

        If System.Double.TryParse(Row.Column, retNum) Then

            Row.DirectRowToNumeric()

        Else

            Row.DirectRowToNonNumeric()

        End If

    End Sub

End Class

In this particular code snippet the row is sent to one of two outputs depending on the result of the TryParse() method, but is up to you what you do with the row.

If you want to know more about multiple outputs from a script component then go here:

SSIS Nugget: Multiple outputs from a synchronous script transform 

I have attached the package that I built containing this code so you can see exactly how it works.

-Jamie

UPDATE, 13th March 2008: Andy Mitchell has devised a way of doing this in the Derived Column component and has posted his little trick below in the comments section. Does his technique work for you? If so, leave a comment and let others know.

Published Thursday, May 03, 2007 11:39 PM by jamie.thomson
Attachment(s): Package.zip

Comments

 

Christoph Seck said:

Yes, the missing "isnumeric" can be really annoying. But in the Scrip Component you actually have an "isnumeric", so "If IsNumeric(Row.Column) ..." should work too.

Another possibilty to overcome the problem could be, doing a typecast in a Derived Column and sending the "bad" rows to the error output. Any idea which possibility is

the fastest?

Thanks for your great blog!

May 4, 2007 8:17 AM
 

jamie.thomson said:

Hi Cristoph,

Both would be synchronous so even if there is a different I'd expect it to be neglegible. Why not test it and try it out?

-Jamie

May 4, 2007 2:52 PM
 

翻译公司 said:

I learned more useful infos from your site. Thanks much!

May 12, 2007 11:08 AM
 

Prabhakar said:

Another way is to use the Data Conversion transformation and directing the numeric rows (rows causing no error) and non-numeric rows (rows causing error) to the appropriate destinations.

January 10, 2008 10:25 PM
 

Andy Mitchell said:

Since this excellent blog has helped me loads to get to grips with SSIS, I thought I would give my ten pence worth.

I was really frustrated with this - and was determined to stick with using a derived column component (I'd already got through mapping 40 odd fields and didnt want to start again using the script component!).

Anyway, I found a hack to get around this issue.

IF NOT NULL(Col) AND ISNUMERIC(Col) THEN

   Result = cLng(Col)

ELSE

   Result = 0

END IF

in DTS

equates to:

Result = !ISNULL([Col]) && ((DT_STR,20,1252) [Col] == ((DT_STR,20,1252) (DT_I4) [Col] )) ? (DT_I4) [Col] : 0

All I do convert Col to an int, then back to a string - and compare it to the original string value.

If they are the same then it's a numeric field.

Also, I believe that because I am checking for null before this happens, the IF statement will jump out when a null is found, stopping the cast to an int from failing.

Seems to work fine for me - hope it helps the rest of you!

March 13, 2008 12:29 PM
 

jamie.thomson said:

Andy,

Great work!

If I'm honest I'll carry on usig the script component for this because I like the lack of verbosity, each to their own of course. I've updated this post accordingly.

To the people that are suggesting attempting to cast the values and then catch all the failed casts in the error output....be wary of this. If a different error occurs how are you going to distinguish them? I'm not saying it won't work...but be wary.

-Jamie

March 13, 2008 12:53 PM
 

Ra Osolage said:

I'm curious how this calculation can be modified to check for decimal values?

May 19, 2008 3:37 PM
 

andy mitchell said:

It's tricky:

IF NOT NULL(Col) AND ISNUMERIC(Col) THEN

  Result = cDbl(Col)

ELSE

  Result = 0

END IF

in DTS

equates to:

Result = !ISNULL([Col]) && ((DT_STR,20,1252) [Col] == ((DT_STR,20,1252) (DT_R8) [Col] )) ? (DT_R8) [Col] : 0

May 27, 2008 5:36 PM
 

K.Larsen said:

Andy,

I tried the trick with converting to an int and back, but I found a problem, when the number started with zero.

Testing '012' resulted in not numeric, as '012' <> '12'

May 28, 2008 10:06 AM
 

Jens Fokdal said:

I think there is a simpler way.

I use a "!isnull((DT_I4) [col])" expression.

It returns true for numeric and null for non-numeric inputs.

June 26, 2008 2:59 PM
 

ranomore said:

Thanks, as always, for a great post! I think the SSIS community would be up a creek without a paddle were it not for your blog!

July 4, 2008 6:31 AM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems