Solved

ssis derived column expression

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

627 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