Tuesday, September 13, 2011

Numeric Checking in SSIS

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
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


No comments:

Post a Comment

Note: Only a member of this blog may post a comment.