[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 461
  • Last Modified:

ssis derived column expression

I want to create a expression for derived column

newcolumn='1'+ACCOUNT_NUMB
where code IN ('11','22','23')
0
Angela4eva
Asked:
Angela4eva
  • 3
1 Solution
 
ValentinoVBI ConsultantCommented:
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
 
ValentinoVBI ConsultantCommented:
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
 
Angela4evaAuthor Commented:
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
 
ValentinoVBI ConsultantCommented:
"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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now