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.
Step 2: Create an SSIS Package
1. Let’s create a new package in SSIS. You can refer to the below image.
2. In the Control Flow add a Data Flow Task as shown below.
3. In the Data Flow Task, add and configure the Excel source (created in step 1). You can refer to the below image.
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.
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.
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.
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:
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.
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.
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.
Let’s execute the package, as you can see from the below image numeric and non numeric data has been successfully divided.
Next Steps
- Don’t forget to practice this tip.
- You can also download and test the package.
- Check out how to Execute SSRS report from SSIS Package.
- Check out tips on SQL Server Reporting Services Parameters.
- Check out all of the SQL Server Business Intelligence Tips.
- Check out my all tips here.
