Numeric Checking in SSIS

A post in MSDN forum drew my attention toward missing functionality of IsNumeric() in SSIS expressions.

To solve this we can use script task and check of IsNumric()
forexample I will use following data and table

--create a test table
create table TEST
(
[id] varchar(20),
[name] varchar(20)
)
--Fill some data

Insert into TEST values ( 1,'Mike')
Insert into TEST values ( 2 ,'Rahul')
--error data with non numeric id
Insert into TEST values ( '3a' ,'Worngdata')

Now lets design our package
1) Get data extract in Oledb DataSoruce
2) Create a Script task and specify input column as ID


2.b) Add a column as IsNumeric of Boolean datatype


2.c) Write below code as Script


If IsNumeric(Row.id) Then
Row.IsNumeric = True
Else
Row.IsNumeric = False
End If

3) Use Conditional Split to separate out bad records and valid records


4) Now when we execute the package it will separate out records with non-numeric data


Full package