How can I check for numerics in a string fileld in a conditional split?

Question

How can I check for numerics in a string fileld in a conditional split?

David Kingman

SSChasing Mays

Points: 625
More actions
March 17, 2009 at 2:46 pm

#128650

How can I check for numerics in a string field in a conditional split of a flat file data source?
I basically want to dump the bad data.
Thanks,
Dave

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

You can check "like '%[0-9]%'". That'll tell you if the field contains any numbers.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

David Kingman SSChasing Mays Points: 625 More actions · Answer 2

Thanks, but with that I get:

"Error at Data Flow Task [Conditional Split [214]]: Attempt to parse the expression "[Column 0] like '%0-9%'" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

Error at Data Flow Task [Conditional Split [214]]: Cannot parse the expression "[Column 0] like '%0-9%'". The expression was not valid, or there is an out-of-memory error.

Error at Data Flow Task [Conditional Split [214]]: The expression "[Column 0] like '%0-9%'" on "output "Case 1" (278)" is not valid.

Error at Data Flow Task [Conditional Split [214]]: Failed to set property "Expression" on "output "Case 1" (278)".

I should have mentioned it's a 10 byte column.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

I don't think it's possible to do a "regEx" search using standard SSIS components (such as Conditional Split) because of non-availability of "regEx" functions in SSIS.

Other option is to do the check in the "Script Component" and flag that row as "Invalid" or "Valid" and then redirect the rows to appropriate outputs using "Conditional Split".

--Ramesh

David Kingman SSChasing Mays Points: 625 More actions · Answer 4

How about using the Regular Expression Editor?

[0-9] seems to work OK most of the time but it's still letting some things through, not sure why. I think this is only looking at the first byte. I found this

^([0-9]|[1-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])$

Which, the description sounded like just what I needed, but it doesn't let anything through.

Any thoughts?