Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Case Statement Functionality in SQL Server Integration Services Derived Column Transformation

MSSQLTips author Dallas Snider By:   |   Read Comments (3)   |   Related Tips: More > Integration Services Control Flow Transformations
Problem

When selecting data from a database using T-SQL, one can use CASE statements to derive new columns based on data in the database. However, when reading data from files in SQL Server Integration Services (SSIS) there isn't a CASE statement readily available when new columns need to be derived or existing values need to be replaced.

Solution

The solution to this problem is to use the Derived Column transformation in SSIS. The image below shows a simple SSIS package that utilizes a Flat File Source, Derived Column Transform, and OLE DB Destination.

use the Derived Column transformation in SSIS

The data used for this example is shown below.

a simple SSIS package

The data types assigned for each column are represented below. This image is from the SQL Server Management Studio's display of the destination table.

the SQL Server Management Studio's display of the destination table

Double-clicking on the Derived Column transformation brings up the Derived Column Transformation Editor.

the Derived Column Transformation Editor

For the first example, we will create a new column named PriceCode with a data type of string with a length of 4. If the value of the Amount column is less than 300, then we set PriceCode equal to "Low", else we set PriceCode equal to "High".

  • We type "PriceCode" in the "Derived Column Name" column.
  • We leave the "Derived Column" column set to the default of "add as new column".
  • Drag the Amount column from the Columns folder above to the "Expression" column text box. Notice how SSIS puts square brackets around the column name by default. Also, notice how the Data Type column defaults to the type specified in the column metadata, which is currency [DT_CY].
Notice how SSIS puts square brackets around the column name by default

At this point we have just copied the data as-is from the Amount column to the newly created PriceCode column. Now, we need to add our logic to set the price code.

We need to add the following code to the Expression text box to set the value of the PriceCode column to "Low" if the Amount is less than 300, else we set PriceCode equal to "High".

(DT_STR, 4, 1252)([Amount] < 300 ? "Low" : "High" )

The syntax in the above statement is (condition_to_be_tested ? value_if_true : value_if_false).  In the above statement, "Amount < 200" is the condition to be tested, the expression following the question mark, "Low", is the true condition while the expression following the colon, "(Amount < 500 ? "Med" : "High")", is the false condition which happens to be another conditional test nested in the statement.

If there is a syntax error with the Expression, the text in the Expression box will be red. If the text is black, then the syntax is correct. By default, the data type for strings is set to Unicode string [DT_WSTR]. We use the Type Cast (DT_STR, 4, 1252) to cast the derived value to a string with a length of 4 using a code page of 1252.

the data type for strings is set to Unicode string [DT_WSTR]

Next, we have to add the PriceCode column to the destination table.

alter table [dbo].[tblTips] add PriceCode varchar(4) null

After adding the column to the destination table, we need to map the PriceCode from the "Available Input Columns" to the "Available Destination Columns" in the OLE DB Destination Editor. Failing to perform this mapping will result in NULL values in the PriceCode column in our destination table. The following image shows the editor before the mapping.

we need to map the PriceCode from the "Available Input Columns"

The following image shows the editor after the mapping.

The following image shows the editor after the mapping

After executing the SSIS package, we execute a query to view the contents of the destination table. We can see in the image below that the PriceCode column is populated as desired.

After executing the SSIS package, we execute a query to view the contents of the destination table

Taking this example a step further, we can add the following code to the Expression text box of the Derived Column Transformation Editor to set the value of the PriceCode column to:

  • "Low" if the Amount is less than 200
  • "Med" if the Amount is greater than or equal to 200 and less than 500
  • "High" if the Amount is greater than or equal to 500

    (DT_STR,4,1252)(Amount < 200 ? "Low" : (Amount < 500 ? "Med" : "High"))
    

    The results of this change are shown below.

    we can add the following code to the Expression text box of the Derived Column Transformation Editor

    Taking this example another step further, we can add the following code to the Expression text box of the Derived Column Transformation Editor to set the value of the PriceCode column to

  • "X" if the Amount is less than 200 or the Category is A
  • "Y" if the Amount is greater than or equal to 200 and less than 400 and the Category is B
  • "Z" if the Amount is greater than or equal to 500
  • "U" if the above conditions are not met

    (DT_STR,4,1252)((Amount < 200 || Category == "A") ? "X" : 
                    (Amount >= 200 && Amount < 400 && Category == "B" ? "Y" : 
                    (Amount >= 500) ? "Z" : "U"))
    

    In the above statement, the double pipe "||" is a logical OR operator, the double ampersand "&&" is a logical AND operator, while the double equal sign "==" is the test for equality operator.  If you are familiar with C/C++, then these operators have the same functionality.

    The results of this change are shown below.

    we can add the following code to the Expression text box of the Derived Column Transformation Editor
    Next Steps


  • Last Update: 4/16/2013


    About the author
    MSSQLTips author Dallas Snider
    Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

    View all my tips


    print tip Print  
    Become a paid author





    join MSSQLTips for free SQL Server tips     



    Learn more about SQL Server tools
    Post a comment or let the author know this tip helped you.

           All comments are reviewed, so stay on subject or we may delete your comment.

    *Name   *Email Notify for updates



           Note: your email address is not published. Required fields are marked with an asterisk (*)


    Get free SQL tips:

    *Enter Code refresh code     



    Tuesday, August 26, 2014 - 8:47:24 PM - JaY Read The Tip

    Can anyone tell me how to write an Expression to change datatype from DT_STR to DT_CY in Derived column transformation editor.

    Thanks a lot in advance


    Friday, May 10, 2013 - 5:10:44 PM - Kshitiz Read The Tip

    Does it make any difference if different code page is used ????

    I changed the destination file to 65001. It is giving me different result.

    I used (DT_STR,14,65001)(DISP_CT == "3" ? "Destroy" : "Ship to Vendor")

    the derived column is giving me "ship to vendor everytime even if the condition (DISP_CT == "3") was met .

    Is there any ways to solve this issue ?


    Tuesday, April 16, 2013 - 8:42:27 AM - Joe Celko Read The Tip

    It is a CASE expression, not a statement. David Rozenshtein did some early work using ABS(), SIGN() and other basic  SQL functions to create logical expression before we got the CASE expression. 




     
    Sponsor Information







    Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
    privacy | disclaimer | copyright | advertise | about
    authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
    Some names and products listed are the registered trademarks of their respective owners.