In
SSIS we don’t have ISNUMERIC () equivalent within the SSIS expression language.
You can't use ISNUMERIC() in an SSIS transform, such as a Conditional Split
Transform or a Derived Column Transform, that doesn't mean you can't check to
see if a field is numeric using the SSIS expression language. Using script task
we can check this, using SQL command in Excel ( SELECT F1 FROM [SHEET1$] WHERE ISNUMERIC(F1)=-1) source we can check this, but in this tutorial I will show how we can check if a field
is numeric without script task.
Create
a sample excel file as shown below
As
you can see from the above data our column has both numeric and string data.
Now I want to check which row is numeric.
As
I said before, there is a way we can use a Derived Column Transform (or
Conditional Split) to check if a field is numeric.
Steps:
1-
Create
Excel source connection
2-
Drag
a Derived Column
After
dragging in a Derived Column Transform into your Data Flow Task, create a new
column to be added as a new column to your data flow. Give it a meaningful name
to the new column and use this expression:
(DT_I4)Column == (DT_I4) Column? 1 : 0
Then
near the bottom of the Derived Column Transform Editor window, click Configure
Error Output. You need to tell SSIS to ignore failure on Error, as seen here:
Optionally, you
could choose to redirect rows that are not numeric to the Error output of the
Derived Column and then handle those rows there.
3-
Add
another derived column and add data viewer on it.
Package
should look like this.
On
Execution you should get the below output.
You
can see that the rows that are not numeric have a NULL value for the IsNumeric
field we created with the Derived Column. Rows that are numeric have a 1. This
way it is easy for us to determine which rows of a certain field is numeric and
which are not numeric by checking for NULL.
I hope you enjoyed the article don't forget to share on Facebook. Please leave your comments below if there are any questions.
No comments:
Post a Comment