Wednesday, December 24, 2008

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( Then
Row.IsNumeric = True
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


Anonymous said...

were should i put the script, plz explain???Provide snapshot, i clicked edit script in the script component and should i go with or c#.????

Anonymous said...

Its very nice,its worked for me.Thanks man

Post a Comment