Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (4)   |   Related Tips: More > Integration Services Data Flow Transformations

Problem

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.

Solution

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


Last Update:






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.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

Hello,

 

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

Thank you so much


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

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

Hi, Ghanesh.

Great post, good job.

Regards.


Learn more about SQL Server tools