SSIS Derived Columns help needed

Question

SSIS Derived Columns help needed

Mr Gauthier

Newbie

Points: 1
More actions
September 30, 2010 at 8:11 am

#239074

Hello,
I am currently looking for ways to parse data from one column into several columns. The type of data is an address (String)
Ex:
1234 NE Anywhere RD APT B
but i also have some data that doesnt include a street number or APT
SW Anywhere Blvd
1234 E Somewhere ST
I need to find a way to take in my data, and derive it into columns NUMBER, DIRECTION, NAME, SUFFIX, APT
I am very new at SSIS, and any help with writing expressions to derive the data would be greatly appreciated!

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

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

I would recommend you take a look at using a Script Component for doing this in a DataFlow. I don't think the other components have smarts the handle this kind of parsing without some really convoluted logic, but I could be wrong.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

I second Alvin. You're going to have to build in a lot of logic rules and what-ifs to make that work.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

I was thiniking after my first reply that searching the Internet for Address Parsing Using VB.Net might prove helpful with the actual VB logic.

I guess I should add this to my list of custom transforms to develop.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

The type of parsing you are looking for is very complicated and not trivial. If you can use third-party solutions, check the commercial CozyRoc Address Parse component. It provides exactly what you need and in addition to that you can purchase reference USPS Database and check against it to confirm an address is valid.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/