Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ssis derived column expression

Posted on 2013-11-26
4
Medium Priority
?
456 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

715 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