Solved

ssis derived column expression

Posted on 2013-11-26
4
450 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 500 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
I have a large data set and a SSIS package. How can I load this file in multi threading?
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

752 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