May 12, 2011 at 3:59 pm
I read that there is no ISNUMERIC equivalent in SSIS. Boo!
That makes it tough to process zip codes from a text file source.
I made a derived column to deal with the Canadian zip codes that come in. Right now it looks like this:
SUBSTRING(POLICY_ZIP,1,1) == "T" || SUBSTRING(POLICY_ZIP,1,1) == "v" || SUBSTRING(POLICY_ZIP,1,1) == "N" ? "00000" : POLICY_ZIP
Each time a new letter is included in the file, the package fails and I have to add another ||. It is really no good.
Instead of adding each letter of the alphabet, could some sort of ascii range be used to include all letters of the alphabet?
Thanks for reading
May 13, 2011 at 1:57 am
Boo indeed.
If you have .NET skills, you could use a script component transformation instead.
You can use ISNUMERIC there 🙂
An example:
http://msdn.microsoft.com/en-us/library/ms136114.aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 13, 2011 at 2:07 am
..could some sort of ascii range be used to include all letters of the alphabet?
Have you tried that? Something like (untested):
POLICY_ZIP >= "A0000" && POLICY_ZIP <= "ZZZZZ" ? "00000" : POLICY_ZIP
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 13, 2011 at 2:10 am
I think the most efficient and maintainable option is to create a regular expression (if you know how, I certainly don't) to validate the zip code and use it in a script component.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 13, 2011 at 2:14 am
Yeah, I agree - and there are plenty of RegEx examples available which would mean that it wouldn't take long to work out, even if you don't know.
But if the poster is not comfortable cutting code, something along the lines of my suggestion might be sufficient in this case.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply