Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ssis derived column expression

Posted on 2013-11-26
4
Medium Priority
?
458 Views
Last Modified: 2016-02-11
I want to create a expression for derived column

newcolumn='1'+ACCOUNT_NUMB
where code IN ('11','22','23')
0
Comment
Question by:Angela4eva
  • 3
4 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39679985
Perhaps something like this?

code=="11" || code=="22" || code=="23" ? "1" + ACCOUNT_NUMB : ACCOUNT_NUMB

But what do you want newcolumn to be set to when code is not 11, 22 or 23?  Currently it will be set to ACCOUNT_NUMB.

In case you're not familiar with this, the expression uses the short IIF statement:  <condition> ? <then> : <else>

And || means OR...
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39679998
Assuming your code field is a string, you could also use FINDSTRING:

FINDSTRING(",11,22,23,","," + code + ",",1) > 0 ? "1" + ACCOUNT_NUMB : ACCOUNT_NUMB
0
 

Author Comment

by:Angela4eva
ID: 39681552
code=="11" || code=="22" || code=="23" ? "1" + ACCOUNT_NUMB : ACCOUNT_NUMB
will this create a new column?

what if i have multiple conditions

like 11 1+account_numb
if '99' 2+account_numb
if '44' or '55'       8+account_numb?
I appreciate your help
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 2000 total points
ID: 39682839
"will this create a new column?"

Yes, that's what the Derived Column transformation does.  I thought you were already referring to that in your question, but I may have misunderstood due to the choice of words...  So, both expressions which I posted go into the Expression box of the Derived Column Transformation.

For more complex cases, such as your multiple conditions, I use the .NET Script transformation.  Let me know if you need help with that...

You are using a Data Flow transformation in SSIS, right?  (otherwise my comments don't make much sense)
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question