Check IsNumeric() with Derived Column Transform in SSIS Package

Who is online?  0 guests and 0 members
Home  »  Blogs  »  DustinRyan  »  Check IsNumeric() with Derived Column Transform in SSIS Package
 
0
/5
Avg: 0/5: (0 votes)

Comments (15)

KeithHyer
KeithHyer said:

Interesting that when you compare, the "{evaluated expression} ? {true part} : {false part}" is returning a NULL.  It's not returning the "0" ( {false part} ) that you specified it should returned if it was a non-numeric value.

But that is still really clever, Dustin - thanks a lot for sharing that one!  Does this work for other data types or is this strictly a numeric comparison "trick"?

9/1/2010
 · 
 
by
DustinRyan
DustinRyan said:

Thanks, Keith. Can't really get around the NULL return when the expression should evaluate to false since the evaluated expression will not evaluate if it cannot convert the value to a numeric.

This little trick does work for other data types. I've also used this with dates.

9/1/2010
 · 
 
by
Seth
Seth said:

Thanks, Dustin.   I adopted your trick in my Conditional Split control and it works.

3/23/2011
 · 
 
by
user076869
user076869 said:
Thanks Dustin. This doesn't return the 0 on the False Condition, as the trick is not in the comparison, but in the "ignore failure". When the numeric casting fails, the entire operation is Ignored and returned NULL for that field, if you definetely need 0, use subsequent ISNULL(Isnumeric)?0:IsNumeric.
11/20/2012
 · 
 
by
DustinRyan
DustinRyan said:
You are correct. In my example above, all that matters is that the rows that are not numeric are identified.
11/20/2012
 · 
 
by
MitchellPearson
Good Stuff Dustin.
12/1/2012
 · 
 
by
JoseChinchilla_SQLJoe
If you need a 0 returned you can wrap it around an ISNULL and change it to 0 otherwise return 1: ISNULL( (DT_I4)CheckForNumeric == (DT_I4)CheckForNumeric ? 1 : 0 ) ? 0 : 1
12/8/2012
 · 
 
by
user539168
user539168 said:
This is great! Thank you for sharing it Dustin.
2/13/2013
 · 
 
by
DustinRyan
DustinRyan said:
I'm glad it was helpful! You're welcome!
2/13/2013
 · 
 
by
cu7482
cu7482 said:
Hi , I am getting the below error: TITLE: Microsoft Visual Studio ------------------------------ Error at Validate LD [Conditional Split [22823]]: The expression "(DT_I4) [Region] == (DT_I4) [Region] ? 1 :0" is not Boolean. The result type of the expression must be Boolean. Error at Validate LD [Conditional Split [22823]]: The expression "(DT_I4) [Region] == (DT_I4) [Region] ? 1 :0" on "output "Case 1" (22923)" is not valid. Error at Validate LD [Conditional Split [22823]]: Failed to set property "Expression" on "output "Case 1" (22923)". ------------------------------ ADDITIONAL INFORMATION: Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------ BUTTONS: OK ------------------------------
8/15/2013
 · 
 
by
DustinRyan
DustinRyan said:
You have an If expression. A conditional split expression must evaluate to either true or false. Shorten your expression to "(DT_I4) [Region] == (DT_I4) [Region]".
8/15/2013
 · 
 
by
DustinRyan
DustinRyan said:
Although it appears you are comparing the [Region] field to itself.
8/15/2013
 · 
 
by
cu7482
cu7482 said:
Thanks Dustin for the reply above expression do worked. I am trying to implement IsNumeric here but the above expression only works if number is pure numeric like '11' but its not working for alphanumeric like '1AA'.
8/15/2013
 · 
 
by
DustinRyan
DustinRyan said:
Then to do that you need to use a Derived Column Transform instead of the Conditional Split Transform. In that case, your initial expression should work.
8/15/2013
 · 
 
by
cu7482
cu7482 said:
I used this expression in Derived Column (DT_I4)a == (DT_I4)a ? 1 : 0 but its behaving the same way as in conditional split . It's not working for alphanumeric
8/15/2013
 · 
 
by
DustinRyan
DustinRyan said:
Did you configure the error settings to ignore error?
8/15/2013
 · 
 
by
cu7482
cu7482 said:
yes I did.
8/15/2013
 · 
 
by
cu7482
cu7482 said:
My apologies its working
8/15/2013
 · 
 
by
DustinRyan
DustinRyan said:
What was the issue?
8/15/2013
 · 
 
by
thesweenist
thesweenist said:
This was a huge time saver and mental energy saver. Thank you!
6/27/2014
 · 
 
by
Blogs RSS Feed

DustinRyan's latest blog posts

Blogs RSS Feed

Latest community blog posts