SSIS package to split address into 3 fields

Question

SSIS package to split address into 3 fields

W4Designs

Old Hand

Points: 345
More actions
June 9, 2015 at 4:09 pm

#297598

I have a table that I am using in a package to create an extract from. In that table is an address field called address that is 255 characters in length. My table also has 3 additional fields called street1, street2 and street3 that are each 50 characters in length because that is the requirement for my extract. I need to split the address field up in such a way that if it is longer than 50 characters, it backs up to the first space in the address prior to character #50, puts that info in street1, then from that cut off point used in street1, puts the next 50 up to the prior blank space in street2, then the remainder in street3. Where the extract will be used only has three 50 character fields so if the data runs more than 150 characters, the street3 data will just have to be truncated. No way around that, but I don't anticipate any address getting close to that long.
I'm assuming I need to use a "derived column transformation" in my data flow. But, I can't figure out how to do what I need to do with a derived column transformation.
Any help would be greatly appreciated.
Thank you in advance.

Viewing 15 posts - 1 through 15 (of 37 total)

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

PB_BI SSCoach Points: 17463 More actions · Answer 1

If you're querying a table just do the split in SQL. There's loads of information out there on doing this, including here: http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/19/how-to-split-a-string-by-delimited-char-in-sql-server/

I'm on LinkedIn

wallace_nw SSC Rookie Points: 40 More actions · Answer 2

wallace_nw

SSC Rookie

Points: 40

June 10, 2015 at 6:34 am

#1804302

.

W4Designs Old Hand Points: 345 More actions · Answer 3

Although I do agree with you that using SQL to do this split makes the most sense and is probably that fastest/easiest way to do it, my requirement is to do it in the package itself using a means other than SQL.

Any help is greatly appreciated.

PB_BI SSCoach Points: 17463 More actions · Answer 4

Here's how to do it using a script task in the data flow:http://bi-polar23.blogspot.co.uk/2008/06/splitting-delimited-column-in-ssis.html

I'm on LinkedIn

David Burrows SSC Guru Points: 65144 More actions · Answer 5

With derived column transformation set it to new column and use an expressions like

SUBSTRING([address ] +REPLICATE(" ",50),1,50)

SUBSTRING([address ] +REPLICATE(" ",100),51,50)

SUBSTRING([address ] +REPLICATE(" ",150),101,50)

*NOTE* Not tested

Far away is close at hand in the images of elsewhere.
Anon.

W4Designs Old Hand Points: 345 More actions · Answer 6

David, this solution will somewhat work, but doesn't exactly do what I need and I'll try my best to explain why:

Since you're using substring for position 1-50, 51-100 and 101-150, you're only getting data in those positions. However, I need to be able to back up to the space prior to character 50 and get the last whole word, then in the next address field, get that data from the end of position 1-50 that was dropped off because it was too long and capture it, plus the next 50 characters from that point, and so on.

Example of info in an address:

123 Chicamauga Avenue South, Across the Street from International Center Square, Apartment Number 17650 Tokiwa-machi Machida

position 1-50:

123 Chicamauga Avenue South, Across the Street fro

Therefore, Street1 would need to get:

123 Chicamauga Avenue South, Across the Street

Street2 would need to get:

from International Center Square, Apartment Number

Street3 would need to get:

17650 Tokiwa-machi Machida

If you do it with simple substrings:

Street1 would actually get:

123 Chicamauga Avenue South, Across the Street

Street2 would actually get:

International Center Square, Apartment Number (which would leave out the word "from" in the original address)

Street3 would actually get:

17650 Tokiwa-machi Machida

Does that make any sense?

I think the solution suggested by PB_BI will provide the same challenge as above but I haven't tried that one yet.

David Burrows SSC Guru Points: 65144 More actions · Answer 7

In that case a script task is probably the way to go but in your case change the code to

start at col 50 and work backwards to first non space then insert enough spaces to pad it it to 50

then repeat this for col 100 and then col 150

then split the string into 50 char strings

Far away is close at hand in the images of elsewhere.
Anon.

David Burrows SSC Guru Points: 65144 More actions · Answer 8

This might be possible using derived columns but would be a very complex expression or using several derived columns to pad the string into 50 column chunks and a final split

Far away is close at hand in the images of elsewhere.
Anon.

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

David Burrows (6/10/2015)
This might be possible using derived columns but would be a very complex expression or using several derived columns to pad the string into 50 column chunks and a final split

It is doable, and indeed complex.

Would be much simpler in a .NET script.

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]

W4Designs Old Hand Points: 345 More actions · Answer 10

David, you jumped WAY over my head with that one!

OK, so a .NET script, I'm up for that. However, I don't know .NET. Anybody willing to write the script for me that will do this?

Thank you!

David Burrows SSC Guru Points: 65144 More actions · Answer 11

W4Designs (6/10/2015)
David, you jumped WAY over my head with that one!

Mine too LOL I just an idea that jumped into my head

I was thinking of adding a new derived column to pad the first address to 50 chars on word boundary

Passing that to another new derived column to pad 2nd

And third to spit the second into 3 columns

Not sure even possible, never tried but I know SSIS allows the output of a derived column to another derived column

OK, so a .NET script, I'm up for that. However, I don't know .NET. Anybody willing to write the script for me that will do this?

Have to think about that one and find some time to experiment

p.s. I bet Koen would know of an easy way for this

Far away is close at hand in the images of elsewhere.
Anon.

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

I'll have a solution for doing did in the SQL query after I come back from lunch.

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]

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

You could try something like the code below, in the query that extracts the data from your source table.

SELECT

CHARINDEX( ' ',REVERSE(yourAddressField), 1),

Street1 = SUBSTRING(yourAddressField, 1, 50 - CHARINDEX( ' ', REVERSE(SUBSTRING(yourAddressField, 1, 50)), 1))

,Street2 =

SUBSTRING(yourAddressField, LEN(SUBSTRING(yourAddressField, 1, 50 - CHARINDEX( ' ',REVERSE(SUBSTRING(yourAddressField, 1, 50)), 1))) + 1

, 50 - CHARINDEX( ' ', REVERSE(SUBSTRING(yourAddressField, (LEN(SUBSTRING(yourAddressField, 1, 50 - CHARINDEX( ' ',REVERSE(SUBSTRING(yourAddressField, 1, 50)), 1))) + 1), 50)), 1))

,Street3 =

SUBSTRING(yourAddressField,

LEN(SUBSTRING(yourAddressField, 1, 50 - CHARINDEX( ' ',REVERSE(SUBSTRING(yourAddressField, 1, 50)), 1))) +

LEN(SUBSTRING(yourAddressField, LEN(SUBSTRING(yourAddressField, 1, 50 - CHARINDEX( ' ',REVERSE(SUBSTRING(yourAddressField, 1, 50)), 1))) + 1

, 50 - CHARINDEX( ' ', REVERSE(SUBSTRING(yourAddressField, (LEN(SUBSTRING(yourAddressField, 1, 50 - CHARINDEX( ' ',REVERSE(SUBSTRING(yourAddressField, 1, 50)), 1))) + 1), 50)), 1))) + 1

, 50 - CHARINDEX( ' ', REVERSE(SUBSTRING(yourAddressField, (LEN(SUBSTRING(yourAddressField, 1, 50 - CHARINDEX( ' ',REVERSE(SUBSTRING(yourAddressField, 1, 50)), 1))) +

LEN(SUBSTRING(yourAddressField, LEN(SUBSTRING(yourAddressField, 1, 50 - CHARINDEX( ' ',REVERSE(SUBSTRING(yourAddressField, 1, 50)), 1))) + 1

, 50 - CHARINDEX( ' ', REVERSE(SUBSTRING(yourAddressField, (LEN(SUBSTRING(yourAddressField, 1, 50 - CHARINDEX( ' ',REVERSE(SUBSTRING(yourAddressField, 1, 50)), 1))) + 1), 50)), 1))) + 1), 50)), 1)

)

FROM yourTable

I didn't promise it would be pretty. :w00t:

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]

W4Designs Old Hand Points: 345 More actions · Answer 14

Thanks Alvin!

My supervisor-imposed requirement is that I NOT use SQL to do the conversion and to use some alternative method inside the package. Obviously SQL is the most simple way to do it. But, that ain't the way she wants it.

My initial thought was to do it with a derived column, although I wasn't quite sure how to do it. But, I guess if I knew C#, VB or .NET, it could also be done any of those ways. I'm kind of at a loss though since SQL is the only method I'm familiar with.

SSIS package to split address into 3 fields

Cookies on SQLServerCentral