« It's Official. I am the 2009 Exceptional DBA of the Year!! | Main | SSIS, Raw Files and Local IO »

IF-Then-Else statement in SSIS Derived Columns

Here is another one of those rarely used and often forgotten little tidbits that hit me like a sharp tack when I sit down.

I have an input file that is fixed width and there are numerous columns that represent numbers, but the text files are coming from an old publishing program known as VISTA. This system, I believe runs on ISAM files (yeah that old). The output of these files is odd, because if you have a negative number, it is represented as such 3.75-, whereas a positive is 3.75. SQL Server 2005 doesn't appreciate 3.75- as an integer so when importing with SSIS I used a conditional statement within a derived column.

[Column 9] is defined as a 11 characters fixed width, the right most character is '-' if it is negative, and ' ' if it is positive. It represents a percentage, so it is defined within the database as decimal(5,2). Within the Derived Column Transformation I need to extract the negative number and here is how I do it:

RIGHT([Column 9],1) == "-" ? ((DT_NUMERIC,5,2)SUBSTRING([Column 9],1,10)) * -1 : (DT_NUMERIC,5,2)SUBSTRING([Column 9],1,10)

Let's break down the above statement into the standard IF-Then-Else.

IF RIGHT([Column9],1) == "-"

 THEN ((DT_NUMERIC,5,2)SUBSTRING([Column 9],1,10)) * -1

ELSE  (DT_NUMERIC,5,2)SUBSTRING([Column 9],1,10)

If you substitute the ? for the Then and the : for the ELSE, it becomes very easy to build conditional statements within Derived Column Transformations.  The other pieces of code simply state that if a find a "-" at the 11th position, multiply the number by -1 (after converting the first 10 characters to a decimal data type) and if the 11th position is not a "-", just convert the first 10 characters.


PrintView Printer Friendly Version

EmailEmail Article to Friend

References (12)

References allow you to track sources for this article, as well as articles that were written in response to this article.
  • Response
    Hello, I found your website @ google and i have read allot of your other posts. I love what u post! I just added you to my Google News Reader . Keep up the good job. Look forward to see more from you in the future.
  • Response
    Response: dAMkjdbu
    IF-Then-Else statement in SSIS Derived Columns - Journal - SSIS - SQL Server Tidbits
  • Response
    This was a really excellent post. In theory Id like to create like this also - getting time and real work to make a great content but what can I say I procrastinate alot and by no means appear to get something done.
  • Response
    Not too long ago, I didnt give lots of thought to giving comments on blog page posts and have placed comments even less. Reading through by way of your pleasant post, will aid me to do so sometimes.
  • Response
    Awesome work there. signed up to your rss feed!
  • Response
    AWFwfx Cheap Phentermine
  • Response
    The Scriptures is my chart
  • Response
    aqaVSzd Buy ha ha ha
  • Response
    Response: ultimate product
    im digging this post. Im gonna start surfing online for similiar info related to this. good looking!
  • Response
    This is really great content. Thanks a lot for this. I put it on my website where you can find latest video game and technology news and reviews . I linked back to your site and bookmarked it so I can see your new posts.
  • Response
    Response: this website
    IF-Then-Else statement in SSIS Derived Columns - Journal - SSIS - SQL Server Tidbits
  • Response
    IF-Then-Else statement in SSIS Derived Columns - Journal - SSIS - SQL Server Tidbits

Reader Comments (5)

Thank you very much for this post. It really helped me out.

October 20, 2009 | Unregistered CommenterGuran

Helped me out too, thank you!

May 11, 2011 | Unregistered CommenterRaeldor

Thanks, helped me as well

September 6, 2012 | Unregistered Commentersydney

Very helpful! Thank you!

February 6, 2013 | Unregistered Commenterstefan

Still useful today. :)

May 30, 2013 | Unregistered CommenterAndrew Kerr

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>