SSIS Expression Help... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

SSIS Expression Help...

Author

Topic

tooba
Posting Yak Master

224 Posts

Posted - 2015-03-19 : 10:53:42

Hi,

Need help. Here is my sample data

ID,Fname,CellPhone,OtherPhone,OID
1,Smith,2039131941,Null,1452
2,James,(203)913-1941,2039131942,6458
3,Chris,Null,(203)913-1943,1111

Here is my requirement.

If Length of CellPhone > 10 or < 10 or Null use OtherPhone
If Lenght of OtherPhone > 10 or < 10 or Null use OID

So far I have this expression in derived column

ContactNumber = (Len(CellPhone) > 10 || Len(CellPhone) < 10 || isnull(CellPhone) ? OtherPhone : CellPhone : (Len(OtherPhone) > 10 || Len(OtherPhone) < 10 || isnull(OtherPhone) ? OID : OtherPhone ))

I am not getting right result what I want, at the end I want below result (as an example)

ID,Fname,ContactInfo
1,Smith,2039131941
2,James,2039131942
3,Chris,1111

Please advise.

Thank You.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-19 : 11:11:50

That is not a valid expression! How is it working for you? In particular, there are two colons in the expression making it look like a three-way conditional expression, but there is no such thing.

e.g. this section is not valid:


... ? OtherPhone : CellPhone : ...

and will not compile. Please post the EXACT expression from the DC component.

tooba
Posting Yak Master

224 Posts

Posted - 2015-03-19 : 11:54:07

Sorry, here is the exp...

Len(CellPhone) > 10 || Len(CellPhone) < 10 || isnull(CellPhone) ? OtherPhone : (Len(OtherPhone) > 10 || Len(OtherPhone) < 10 || isnull(OtherPhone) ? OID : OtherPhone

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-19 : 12:03:57

OK -- I simulated this. using your DC expression and data from your first line, for your first row I got:

ID,Fname,ContactInfo
1 ,Smith,1452

What do you actually wan to see?

tooba
Posting Yak Master

224 Posts

Posted - 2015-03-19 : 12:06:02

I want to see

ID,Fname,ContactInfo
1,Smith,2039131941
2,James,2039131942
3,Chris,1111

Thank You for your help!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-19 : 12:18:02

OK -- so the expression says:

if the cell phone number is null or not exactly 10 digits, then if the other phone is null or not exactly 10 digits, choose the OID.

In the first row, the cell phone number IS exactly 10 digits and the other phone IS null, so the OID is selected.

In other words, your expression does exactly what you said in words:

quote:

If Length of CellPhone > 10 or < 10 or Null use OtherPhone
If Lenght of OtherPhone > 10 or < 10 or Null use OID

However, your desired output does NOT match the above conditions. I think you need to clarify your conditions.

tooba
Posting Yak Master

224 Posts

Posted - 2015-03-19 : 12:25:16

Here is my source data (as an example)

ID,Fname,CellPhone,OtherPhone,OID
1,Smith,2039131941,Null,1452
2,James,(203)913-1941,2039131942,6458
3,Chris,Null,(203)913-1943,1111

I want, If Length of CellPhone > 10 or < 10 or Null Use OtherPhone, ( In first row Len of CellPhone is not > 10 or < 10 or Null, so it should Pick 2039131941)
Second Row, CellPhone is Null and len of OtherPhone is not > 10 or < 10 or Null, so it should pick 2039131942
Third Row, CellPhone is Null and Len of Other Phone is > 10 , so it should Pick 1111

Please guide me, if I am missing something. My expression is not 100% right that's why I am getting wrong data. I need help to fix my expression.

Thank You.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-19 : 13:43:45

But your expression does not return CellPhone in any of the branches:

Len(CellPhone) > 10 || Len(CellPhone) < 10 || isnull(CellPhone) ? OtherPhone : (Len(OtherPhone) > 10 || Len(OtherPhone) < 10 || isnull(OtherPhone) ? OID : OtherPhone

so since the first row has a cellphone that is 10 digits, it takes the "else" branch:

(Len(OtherPhone) > 10 || Len(OtherPhone) < 10 || isnull(OtherPhone) ? OID : OtherPhone

Here, OtherPhone is null so it takes OID.

How about just:

LEN(CellPhone) == 10 ? CellPhone : LEN(OtherPhone) == 10 ? OtherPhone : OID

tooba
Posting Yak Master

224 Posts

Posted - 2015-03-19 : 13:58:09

I test your exp

LEN(CellPhone) == 10 ? CellPhone : LEN(OtherPhone) == 10 ? OtherPhone : OID

First and second rows looks good, third row I am getting Null.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-19 : 14:16:15

LEN(CellPhone) == 10 && !ISNULL(CellPhone) ? CellPhone : LEN(OtherPhone) == 10 && !ISNULL(OtherPhone) ? OtherPhone : OID

tooba
Posting Yak Master

224 Posts

Posted - 2015-03-19 : 14:31:30

Looks great. Thank You for your Help!!

This question answered by gbritton last reply.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources