Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

«December 2015»

T-SQL Tuesday #73 Invitation – Naughty or Nice?

Hello Dear Reader!  This is the first Tuesday of the month and you know what that means.  It’s time to announce the T-SQL Tuesday Topic of the month!  This is your opportunity to participate in the largest SQL Blog party on the intrawebs. 

T-SQL Tuesday is an event started by Adam Machanic(@AdamMachanic| Blog) back in 2009.  The basic idea one blogger hosts the party and others participate.  We announce the topic the first Tuesday of the month, December 1st 2015 for today, and everyone will post their blogs on the second Tuesday of the month, December 8th 2015 for the actual posts.  This month the host is none other than……….. drum roll………….. ME!

Read more

Check IsNumeric() with Derived Column Transform in SSIS Package

  • 1 September 2010
  • Author: DustinRyan
  • Number of views: 83020

At some point or another, you've probably run into the road block that is the lack of an ISNUMERIC() equivalent within the SSIS expression language. While 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 (If you feel so inclined, you can use a Script Task to check if a field is numeric. Tim Murphy covers that in his blog here).

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. 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 and use this expression:

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


check for numeric with derived column

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:

Ignore failure

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.

Now when we run the Data Flow Task, we should see results like this:

IsNumeric results with Derived Column

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 are numeric and which are not numeric by checking for NULLs in our field called IsNumeric.

Rate this article:


Other posts by DustinRyan

Please login or register to post comments.