Validate Numeric or Non-Numeric Data in SQL Server Integration Services without the Script Task

By:   |   Comments (7)   |   Related: More > Integration Services Data Flow Transformations


In SQL Server Integration Services we donít have an ISNUMERIC() equivalent function within the SSIS expression language. If you know C# or VB, you can code a script task to check if data is numeric or non-numeric, but if you don't know how to code in C# or VB you might be in trouble. Luckily in this tip I will show how to check if data is numeric or non-numeric without using a script task.


This tip assumes that you have previous real world work experience building a simple SSIS package. In this tip I will describe how to check if data is numeric or non-numeric without using a script task. To demonstrate the solution, I will use SQL Server Integration Services 2012 and Microsoft Excel 2012.

Step 1: Create Sample Data

Let's create some sample data in Excel. As you can see in the below image our sample data has both numeric and non-numeric data in the same column. Now our task is to find which rows have numeric or non-numeric data.

Excel Sample Data

Step 2: Create an SSIS Package

1. Let's create a new package in SSIS. You can refer to the below image.

Create SSIS Package

2. In the Control Flow add a Data Flow Task as shown below.

Add Data Flow Task

3. In the Data Flow Task, add and configure the Excel source (created in step 1). You can refer to the below image.

Add Excel Source Task In Data Flow Task

Make sure the Excel Source is configured correctly. After successful configuration, you should be able to preview the data as shown in the below image.

Sample Data Preview in Excel Source

Step 3: Check for numeric or non-numeric data

1. In the Data Flow Task, add a Derived Column transformation just after the Excel Source task. Create a connection from the Excel Source task to the Derived Column transformation. You can refer to the below image.

Add Drived Column Task

2. Open the Derived Column Transformation, create a new column and give it a meaningful name. In my case I called it Check_Numeric_NonNumeric as the Derived Column Name and used the below expression.

(DT_I4)F1 == (DT_I4) F1? 1 : 0

In the above expression, F1 is the Excel Source Output Column name, change it according to your Excel Source Output Column name. After the changes the Derived Column transformation should look like the below image.

Add New Column In Drived Column Transformation

3. Near the bottom of the Derived Column transform editor window, click Configure Error Output as shown above. You will need to tell SSIS to Ignore Failure on Error, as shown below:

Error Configuration For Newly Added Column

4. Add one more Derived Columns after the first Derived Column and name it Numeric and Non-Numeric Flag. Create a connection from the first Derived Column to the Numeric and Non-Numeric Flag Derived Column and add a Data Viewer on the connection. After the changes, the package should look like the below image.

Add Another Derived Column After First Derived Column

Step 4: Execute Package

We have done all the necessary changes, now let's execute the package. As you can see from the below image, on successful execution the Data Viewer will show two columns; F1 and Check_Numeric_NonNumeric columns. You can see that the rows that are not numeric have a NULL value for the Check_Numeric_NonNumeric column; rows that are numeric have a value of 1. This way it is easy for us to determine which rows of a certain field are numeric and which are not numeric by checking for NULL.

Data Viewer Result After Package Execution

Step 5: Numeric and Non Numeric Data Splitting

Let's split the sample data into numeric data and non-numeric data.

Let's add a Conditional Split after the Check_Numeric_NonNumeric_Flag Derived Column as shown below. Add Data Viewers on the Numeric and Non Numeric Conditional Split connection.

Package Numeric NonNumeric Data Split

Let's execute the package, as you can see from the below image numeric and non numeric data has been successfully divided.

Conditional Split Numeric Non-Numeric Data Viewer Result
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ghanesh Prasad Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, April 1, 2019 - 4:09:14 AM - יניב Back To Top (79436)

good implement , helps me , thanks.

Friday, January 4, 2019 - 9:36:17 AM - Vincenzo Back To Top (78623)


Fantastic job.


Monday, July 2, 2018 - 1:29:33 AM - Parimal Mishra Back To Top (76496)

How is it possible when the excel sheet consists of more than one column and the column which we are filtering out has entries in varchar and we need to filter out only those entries which are completely numeric along with all other column values

Wednesday, April 26, 2017 - 8:00:50 AM - Ally Back To Top (55204)



How to convert non numeric value to NULL in ssis derived column transformation editor?

I mean if the value is not numeric  then I'd like to convert it either NULL or 0 value.


Thanks for your help

Tuesday, August 25, 2015 - 4:32:06 AM - Sandeep Kumar Back To Top (38535)

Thank you so much

Monday, August 24, 2015 - 12:49:02 PM - Beverly Yan Back To Top (38522)

Hi Ghanesh, 

Very SMART workaround. Great Sharing! I will save it for future use. 

Best Wishes

Monday, August 24, 2015 - 8:30:54 AM - Junior Galv„o - MVP Back To Top (38520)

Hi, Ghanesh.

Great post, good job.


get free sql tips
agree to terms