convert Phone Number

Question

Post reply

convert Phone Number

Isabelle2378

SSCertifiable

Points: 6174
More actions
August 29, 2006 at 12:31 pm

#113486

Hi,
I'm trying to bring over phone numbers from our legacy system into our new ERP system. It is char(20) in the old system and nchar(16) in the new so I setup a data conversion for that field to a datatype Unciode string [DT_WSTR] length 50 (the default). The phone numbers in our legacy system vary:
2015877052
800 554-6474
(650) 424-9401
8004519985 #26207
5105355400 x 3433
(925) 640-1676-CELL
The format in our new system is (123) 456-7890, although there are a few records that look like:
510-535-5400x343
7073992231
847)439-8570X1
My package deletes everything from the table prior to converting the data and trying to insert it into the new system tables, but I keep getting the following error:
[OLE DB CIDMAS [8136]] Error: There was an error with input column "VENDOR_PHONE" (31101) on input
"OLE DB Destination Input" (8149). The column status returned was: "The value could not be converted
because of a potential loss of data.".
Any ideas what I may be doing wrong or what I am missing?
Thanks!
Isabelle
Thanks!
Bea Isabelle

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Check out the max len of that column... Maybe it's over your threshold.

Also check that you're not losing any data between Nchar and char.

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 2

I'm new to SSIS. Where do I check that? The max length of the column on the old legacy side or the new ERP table side? I know that some of the lengths coming from my old system are more than 16, which is the size of the new ERP column. I tried to use ignore failure for truncation and it still errors.

Thanks,

Isabelle

Thanks!
Bea Isabelle

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I rarely used DTS and never used SSIS. I'd suggest firing up the management studio and run the queries manually. I usually did those checks like this.

Select * from MYTable where MyCol <> CONVERT(newtype, MyCol)

This is a good place to start, but it may not give you the answer you seek.

Ram Ram Ten Centuries Points: 1079 More actions · Answer 4

You may try this to convert and then do the insert

SET NOCOUNT ON

DECLARE @tbl TABLE (A VARCHAR(30),B VARCHAR(30))

INSERT INTO @tbl(A) SELECT '2015877052'

INSERT INTO @tbl(A) SELECT '800 554-6474'

INSERT INTO @tbl(A) SELECT '(650) 424-9401'

INSERT INTO @tbl(A) SELECT '8004519985 #26207'

INSERT INTO @tbl(A) SELECT '5105355400 x 3433'

INSERT INTO @tbl(A) SELECT '(925) 640-1676-CELL'

SELECT '('+

SUBSTRING(

(CONVERT(VARCHAR(30),LEFT(((REPLACE(REPLACE((REPLACE((REPLACE((REPLACE(A,'(','')),')','')),' ','')),'-',''),'#',''))),

PATINDEX('%[^0-9]%',((REPLACE(REPLACE((REPLACE((REPLACE((REPLACE(A,'(','')),')','')),' ','')),'-',''),'#','')))+' ')-1)))

,1,3)+') '+

SUBSTRING(

(CONVERT(VARCHAR(30),LEFT(((REPLACE(REPLACE((REPLACE((REPLACE((REPLACE(A,'(','')),')','')),' ','')),'-',''),'#',''))),

PATINDEX('%[^0-9]%',((REPLACE(REPLACE((REPLACE((REPLACE((REPLACE(A,'(','')),')','')),' ','')),'-',''),'#','')))+' ')-1)))

,4,3)+'-'+

SUBSTRING(

(CONVERT(VARCHAR(30),LEFT(((REPLACE(REPLACE((REPLACE((REPLACE((REPLACE(A,'(','')),')','')),' ','')),'-',''),'#',''))),

PATINDEX('%[^0-9]%',((REPLACE(REPLACE((REPLACE((REPLACE((REPLACE(A,'(','')),')','')),' ','')),'-',''),'#','')))+' ')-1)))

,7,4)

FROM @tbl

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 5

Wow. I used your suggestion and it did work great. But I'm trying to do this using SSIS and I have an area in the Derived Column area but not sure this will work there. I will give it a try.

Thanks for your suggestion!

Isabelle

Thanks!
Bea Isabelle