June 10, 2015 at 12:27 pm
W4Designs (6/10/2015)
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.
I'd question your supervisor on that one.
You can do the same thing (I think) using expressions in the Derived Column transform. You can take the code for each street filed and convert the SQL syntax using the proper expression functions. It won't be any prettier.
If you go the Derived Column route, you can make the code a bit cleaner.
If you generate the value for each street fields in separate transforms, then the transform for Street2 can make use of what you already generated for Street1, and similarly for Street3.
For example, for Street2:
LEN(SUBSTRING(yourAddressField, 1, 50 - CHARINDEX( ' ',REVERSE(SUBSTRING(yourAddressField, 1, 50)), 1)))
becomes:
LEN(Street1)
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]
June 10, 2015 at 12:36 pm
I'd question your supervisor on that one.
Ahhhh, if it were only that easy. Actually, I did inquire why I couldn't simply use SQL and the reply was basically a "because i said so".
Derived column doesn't like charindex at all. I tried the following and got an error that charindex was not recognized.
LEN(SUBSTRING(Street, 1, 50 - CHARINDEX( " ",REVERSE(SUBSTRING(Street, 1, 50)), 1)))
June 10, 2015 at 12:43 pm
The syntax I posted is all SQL. If I'm not mistaken, FINDSTRING might be what you need to use instead of CHARINDEX in SSIS expressions.
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]
June 10, 2015 at 12:49 pm
In addition to my previous reply, i did just discovered that the equivalent to charindex in a derived column is findstring.
June 10, 2015 at 12:53 pm
OK, so for street2, I used the following and got no errors. I'm not sure how to code street1 or street3 though. Or actually, reading the code below, it looks like that should be street1. No?
LEN(SUBSTRING(Street,1,50 - FINDSTRING(" ",REVERSE(SUBSTRING(Street,1,50)),1)))
However, after running it and checking my table, I don't see any change. Do I have to put another step in teh data flow after the derived column to get it to update the source table?
June 10, 2015 at 1:03 pm
See if this works:
Street1: SUBSTRING(Street,1,50 - FINDSTRING(" ",REVERSE(SUBSTRING(Street,1,50)),1))
Street2: SUBSTRING(Street,LEN(Street1) + 1, 50 - FINDSTRING(" ",REVERSE(SUBSTRING(Street,LEN(Street1) + 1,50)),1))
Street3: SUBSTRING(Street,LEN(Street1) + LEN(Street2) + 1, 50 - FINDSTRING(" ",REVERSE(SUBSTRING(Street,LEN(Street2) + 1,50)),1))
This will only work if you use 3 separate derived column transforms.
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]
June 10, 2015 at 1:11 pm
It doesn't like address_2 and address_3. Those are actually what the derived fields are named: Address_1, address_2, address_3. The primary field is called Street. Here's how I modified yours with the correct field names. I think I had the names incorrect in my original post.
Address_1: SUBSTRING(Street,1,50 - FINDSTRING(" ",REVERSE(SUBSTRING(Street,1,50)),1))
Address_2: SUBSTRING(Street,LEN(Address_1) + 1, 50 - FINDSTRING(" ",REVERSE(SUBSTRING(Street,LEN(Address_1) + 1,50)),1))
Address_3: SUBSTRING(Street,LEN(Address_1) + LEN(Address_2) + 1, 50 - FINDSTRING(" ",REVERSE(SUBSTRING(Street,LEN(Address_2) + 1,50)),1))
June 10, 2015 at 1:17 pm
W4Designs (6/10/2015)
It doesn't like address_2 and address_3. Those are actually what the derived fields are named: Address_1, address_2, address_3. The primary field is called Street. Here's how I modified yours with the correct field names. I think I had the names incorrect in my original post.
Address_1: SUBSTRING(Street,1,50 - FINDSTRING(" ",REVERSE(SUBSTRING(Street,1,50)),1))Address_2: SUBSTRING(Street,LEN(Address_1) + 1, 50 - FINDSTRING(" ",REVERSE(SUBSTRING(Street,LEN(Address_1) + 1,50)),1))
Address_3: SUBSTRING(Street,LEN(Address_1) + LEN(Address_2) + 1, 50 - FINDSTRING(" ",REVERSE(SUBSTRING(Street,LEN(Address_2) + 1,50)),1))
Are you doing this in 3 separate derived column transforms?
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]
June 10, 2015 at 1:23 pm
Alvin Ramard (6/10/2015)
Are you doing this in 3 separate derived column transforms?
Yes.
June 10, 2015 at 1:28 pm
W4Designs (6/10/2015)
Alvin Ramard (6/10/2015)
Are you doing this in 3 separate derived column transforms?
Yes.
Ok. Then make sure that you drag the new column name (Address_1 & Address_2) from the column list into the new expression, instead of typing the names.
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]
June 10, 2015 at 1:39 pm
ok, this is what I have now and it does run. but the data is weird:
Address_1:
SUBSTRING(Street,1,50 - FINDSTRING(" ",REVERSE(SUBSTRING(Street,1,50)),1))
Address_2:
SUBSTRING(Street,LEN(address_1) + 1,50 - FINDSTRING(" ",REVERSE(SUBSTRING(Street,LEN(address_1) + 1,50)),1))
Address_3:
SUBSTRING(Street,LEN(address_1) + LEN(address_2) + 1,50 - FINDSTRING(" ",REVERSE(SUBSTRING(Street,LEN(address_2) + 1,50)),1))
However, if the address is longer than 50 characters, it now puts the same first 50 characters from that is in address_1 into address_2 and address_3, nothing more, nothing less.
June 10, 2015 at 1:50 pm
W4Designs (6/10/2015)
ok, this is what I have now and it does run. but the data is weird:Address_1:
SUBSTRING(Street,1,50 - FINDSTRING(" ",REVERSE(SUBSTRING(Street,1,50)),1))Address_2:
SUBSTRING(Street,LEN(address_1) + 1,50 - FINDSTRING(" ",REVERSE(SUBSTRING(Street,LEN(address_1) + 1,50)),1))Address_3:
SUBSTRING(Street,LEN(address_1) + LEN(address_2) + 1,50 - FINDSTRING(" ",REVERSE(SUBSTRING(Street,LEN(address_2) + 1,50)),1))However, if the address is longer than 50 characters, it now puts the same first 50 characters from that is in address_1 into address_2 and address_3, nothing more, nothing less.
FINDSTRING and CHARINDEX don't work the same way. 😉
Try:
Address_1:
SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50)), " ",1))
Address_2:
SUBSTRING(Street,LEN(address_1) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(address_1) + 1, " ",50)),1))
Address_3:
SUBSTRING(Street,LEN(address_1) + LEN(address_2) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(address_2) + 1,50)), " ", 1))
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]
June 10, 2015 at 2:00 pm
Alvin Ramard (6/10/2015)FINDSTRING and CHARINDEX don't work the same way. 😉
Try:
Address_1:
SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50)), " ",1))Address_2:
SUBSTRING(Street,LEN(address_1) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(address_1) + 1, " ",50)),1))Address_3:
SUBSTRING(Street,LEN(address_1) + LEN(address_2) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(address_2) + 1,50)), " ", 1))
Yup, I noticed it wasn't splitting in the correct place.
Address_1 and 3, no errors. Address_2 is giving me the following.
Error at Split Address [Derived Column [2]]: The function "SUBSTRING" requires 3 parameters, not 4 parameters. The function name was recognized, but the number of parameters is not valid.
June 10, 2015 at 2:09 pm
W4Designs (6/10/2015)
Alvin Ramard (6/10/2015)FINDSTRING and CHARINDEX don't work the same way. 😉
Try:
Address_1:
SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50)), " ",1))Address_2:
SUBSTRING(Street,LEN(address_1) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(address_1) + 1, " ",50)),1))Address_3:
SUBSTRING(Street,LEN(address_1) + LEN(address_2) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(address_2) + 1,50)), " ", 1))Yup, I noticed it wasn't splitting in the correct place.
Address_1 and 3, no errors. Address_2 is giving me the following.
Error at Split Address [Derived Column [2]]: The function "SUBSTRING" requires 3 parameters, not 4 parameters. The function name was recognized, but the number of parameters is not valid.
Probably a missing parentheses problem. The code I posted seems to have the correct number of parameters.
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]
June 10, 2015 at 2:16 pm
CRAP!!! We're are right there and I don't see the problem either. I've matched every paren and they all match up unless this mess has made me go blind.
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply