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.

Comments

[Guran]

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

[Raeldor]

Helped me out too, thank you!