We help IT Professionals succeed at work.

How to do formatting Phone Numbers in ssis usign expressions usign derived column

srionline2k6
srionline2k6 asked
on

I need to do phone number formatting in ssis. I get the phone number in the the following format ...
"803-788-7884". The requirement is to be without "-" so it should be finally in this format 8037887884. There might be Nulls or empty spaces and also i need to trim it so that there will not be any truncation for the values while doing substring.

how do i go about it. I did some work on it. I was able to do the following stuff but I am getting error in the derived column itself. It is not accepting the below expression. There might be some parsing error.

Can you throw light on it on where I am going wrong.


ISNULL(Phone_num)||TRIM(Phone_num)==""?NULL(DT_WSTR,5):(DT_STR,10,1252)(SUBSTRING([Phone_num],1,3) + SUBSTRING([Phone_num],5,3) + SUBSTRING([Phone_num],9,4)

Substring part ((DT_STR,10,1252)(SUBSTRING([Phone_num],1,3) + SUBSTRING([Phone_num],5,3) + SUBSTRING([Phone_num],9,4) ) is working fine but if we have NULLS it is giving us NUL as we are doing substring to the NULL but the ISNULL and trimming part is the one that is causing problems.

Also What is wrong in this expression. When I use this follwoing expression REPLACE([Phone_num], "-","")
it is saying truncation error.
Comment
Watch Question

View Solution Only

Christopher GordonSenior Developer Analyst

Commented:
Are you using REPLACE([Phone_num], "-","") in conjunction with any other code?   I.e. is it nested in a substring or anything like that?  I don't believe I've ever seen that expression fail before.
8080_Diver

Commented:
Question 1: Are you doing this in a Script Task or in a SQL Statement?

Question 2: What is the datatype of [Phone_num]?
TempDBA

Commented:
<<When I use this follwoing expression REPLACE([Phone_num], "-","")
it is saying truncation error.>>

It occurs generally when you are trying to put the data into some place where it doesn't have sufficient length to accept the data. Are you inserting data into some small lenght variable or in some column where the data type length defined is lesser than the output.
srionline2k6

Author

Commented:
no i am only using the expression REPLACE([Phone_num], "-","") but its throwing  truncation error during run time and its stopping at the destination target component

But if i use the following expressions its working fine but because this expression requires triming of the spaces and to prevent truncation of the phone number values. i should have to use the trimming and for replacing the null values i should have to do some formatiing rather than using the follwoing expression.....

NULL(DT_WSTR,5):(DT_STR,10,1252)(SUBSTRING([Phone_num],1,3) + SUBSTRING([Phone_num],5,3) + SUBSTRING([Phone_num],9,4)

Please guide me where i am going wrong?
srionline2k6

Author

Commented:
Thanks for the replies... I still not able to figure out where i am going wrong..

@8080_Diver:

Question 1: Are you doing this in a Script Task or in a SQL Statement?
I am using derived column in ssis as an expression.


Actually my destination is Oracle table and it has data type of VARCHAR2(64 BYTE)

it has got enough space to accomodate the value.


@TempDBA... it has got enough space to accomodate as 60 char space. I tried to not to restrict the value to 12 or 10 but its not wrking in either way.

As i said the following wrks fine for me but if it has got NULL i am seeing the NUL value instead of NULL value.
 (DT_STR,10,1252)(SUBSTRING([Phone_num],1,3) + SUBSTRING([Phone_num],5,3) + SUBSTRING([Phone_num],9,4)  

Appreciate your kind responses..
Solution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT
Commented:
You can use something like this

LEN(SpecDesc) > 20 ? SUBSTRING(SpecDesc,1,20) : SpecDesc
Reza RadConsultant, Trainer
CERTIFIED EXPERT

Commented:
you can use Regular expressions for checking input string pattern, and create output string pattern,
a sample of using regular expressions in SSIS is here:
http://rad.pasfu.com/
Reza RadConsultant, Trainer
CERTIFIED EXPERT

Commented:
srionline2k6

Author

Commented:
Thanks Guys for the response