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.
