none
CASE statement in SSIS

    Question

  • I have used the CASE statement below in the derived column transformation process in SSIS but it doesn't seem to work; all I'm getting is error. Could someone please help me look at this and tell me how this can be done in SSIS environment using SQL Server 2008: Thanks for your precious time

    SELECT CASE WHEN ((dbo.Mstr_Sap_Employee.Emp_Group_ID = '1' OR dbo.Mstr_Sap_Employee.Emp_Group_ID = '6')

    AND dbo.Mstr_Sap_Employee.Personnel_Subarea_Code <> '0034') THEN 'Active'

    WHEN (dbo.Mstr_Sap_Employee.Emp_Group_ID = '3' OR dbo.Mstr_Sap_Employee.Emp_Group_ID = 'S') THEN 'Inactive'

    WHEN dbo.Mstr_Sap_Employee.Emp_Group_ID = 'M' THEN 'External'

    WHEN dbo.Mstr_Sap_Employee.Emp_Group_ID = '9' THEN 'Contractor' ELSE 'Withdrawn' END

    Friday, January 21, 2011 4:04 PM

Answers

  • Hello,

    please try this

    ((dbo.Mstr_Sap_Employee.Emp_Group_ID =="1" || dbo.Mstr_Sap_Employee.Emp_Group_ID =="6") && (dbo.Mstr_Sap_Employee.Personnel_Subarea_Code !="0034"))  ? "ACTIVE" :((dbo.Mstr_Sap_Employee.Emp_Group_ID =="3" || dbo.Mstr_Sap_Employee.Emp_Group_ID = "S")? "Inactive" :(dbo.Mstr_Sap_Employee.Emp_Group_ID) =="M"? "External":(dbo.Mstr_Sap_Employee.Emp_Group_ID =="9")? "Contractor" :"Withdrawn" 

     

     

    helpful link

    http://www.programmersedge.com/?p=55

     

    Thanks

    • Marked as answer by lynanee6164 Friday, January 21, 2011 9:17 PM
    Friday, January 21, 2011 4:15 PM

All replies

  • Hello,

    please try this

    ((dbo.Mstr_Sap_Employee.Emp_Group_ID =="1" || dbo.Mstr_Sap_Employee.Emp_Group_ID =="6") && (dbo.Mstr_Sap_Employee.Personnel_Subarea_Code !="0034"))  ? "ACTIVE" :((dbo.Mstr_Sap_Employee.Emp_Group_ID =="3" || dbo.Mstr_Sap_Employee.Emp_Group_ID = "S")? "Inactive" :(dbo.Mstr_Sap_Employee.Emp_Group_ID) =="M"? "External":(dbo.Mstr_Sap_Employee.Emp_Group_ID =="9")? "Contractor" :"Withdrawn" 

     

     

    helpful link

    http://www.programmersedge.com/?p=55

     

    Thanks

    • Marked as answer by lynanee6164 Friday, January 21, 2011 9:17 PM
    Friday, January 21, 2011 4:15 PM
  • The derived column transformation does not accept SQL statements. It uses Expressions.

    In your scenario it seems to better use an Execute SQL Task.

     


    Arthur My Blog
    By: TwitterButtons.com
    Friday, January 21, 2011 4:15 PM
    Moderator
  • You have a number of options.

    First the SSIS Derived Column task does not currently support CASE or SWITCH statements; however, you can effective accomplish the same thing by using a conditional expression in the following format.

    ({Boolean Expression}?{True Part}:{False Part})

    This will require several of these statments nested to achieve the logic you posted above.  Becuase nesting these espressions can be difficut to read, I would suggest that rather than using the derived column task, the case statement could be added to the source query and derive a new column.

    SELECT CASE WHEN ((dbo.Mstr_Sap_Employee.Emp_Group_ID = '1' OR dbo.Mstr_Sap_Employee.Emp_Group_ID = '6')

    AND dbo.Mstr_Sap_Employee.Personnel_Subarea_Code <> '0034') THEN 'Active'

    WHEN (dbo.Mstr_Sap_Employee.Emp_Group_ID = '3' OR dbo.Mstr_Sap_Employee.Emp_Group_ID = 'S') THEN 'Inactive'

    WHEN dbo.Mstr_Sap_Employee.Emp_Group_ID = 'M' THEN 'External'

    WHEN dbo.Mstr_Sap_Employee.Emp_Group_ID = '9' THEN 'Contractor' ELSE 'Withdrawn' END

    Friday, January 21, 2011 4:19 PM
  • you should use conditional statement with this structure:

    (condition) ? (true statement) : (false statement)

     

    your query translated to expression will be:

    ((Emp_Group_ID= ="1" || Emp_Group_ID== "6") && Personnel_Subarea_Code != "0034")  ? "Active" :

    (

    (Emp_Group_ID == "3"  || Emp_Group_ID == "S") ? "Inactive" :

    (Emp_Group_ID == "M" ? "External" :

    (Emp_Group_ID == "9" ? "Contractor" : "Withdrawn"))

    )


    http://www.rad.pasfu.com
    Friday, January 21, 2011 4:20 PM
    Moderator
  • I think that you are writing it like a SQL expresion and not as a SSIS expresion. Loose the Select Case WHENE Statement an change those AND and OR to the data types that ssis understands, && and ||. If this fails try reading this article http://msdn.microsoft.com/en-us/library/ms141069.aspx.
    Friday, January 21, 2011 4:21 PM
  • Thanks Aamir but it's still not working. Still gives error
    Friday, January 21, 2011 4:41 PM
  • What Error Message you are getting? Please insert the correct columns name, you will See a column Tab under Variables on left top side, Click on Columns and then drag them to the expression.

    Where you able to put the whole expressions in the derived column , because sometime if there is line feed then when you paste it only insert the first line, so please copy the above expression and paste into notepad and make them one line and then copy and paste in your derived column.

    Thanks

    Friday, January 21, 2011 4:48 PM
  • Using Execute SQL Task would be on the control flow tab; shouldn't the transformation process be done only on the data flow tab? How will it be incoporated into data flow
    Friday, January 21, 2011 4:55 PM
  • I pointed you to the Execute SQL Task because I thought it would require minimal efforts from your side given you have your SQL statement working, thus also eliminating the need for dealing with the Expression.

    The post

    http://blogs.msdn.com/b/msdnts/archive/2006/11/17/amazing-ssis-parameters-in-execute-sql-task.aspx

    contains a walkthrough for a similar case.

    A missing piece of the puzzle is how you consume your end result [of the query], but that can be figured out later.


    Arthur My Blog
    By: TwitterButtons.com
    Friday, January 21, 2011 5:01 PM
    Moderator
  • Little confuse, Are you Extracting data from some source and then using derived column transformation to implement above expression?

    Or you are using Execute SQL Task?

     

    Can you please let us know the senario with little details so you can get better help. 

    If you are in Data flow and you are extracting data from SQL server then you can just write this query in OLEDB Source , by using CASE statement.

    If you are in Data flow task and then your source is flat file or execel file then you need to use derived column to implement this logic.

     

    Please let us know the details.

    Thanks

    Friday, January 21, 2011 5:01 PM
  • There's no corresponding column in the source table. This code is to insert data based on these conditions into the destination table so I couldn't make use of that tab - column.

    Yes, I inserted the whole expressions in the derived column. I also included the column name of the destination table. The error message says "the expression might contain an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis."

    Thanks 

    Friday, January 21, 2011 5:15 PM
  • There's no corresponding column in the source table. This code is to insert data based on these conditions into the destination table so I couldn't make use of that tab - column.
    Perhaps you can extract and insert in one hit similar to this http://decipherinfosys.wordpress.com/2008/03/15/ssis-execute-sql-task/ using just one Execute SQL Task, why not?
    Arthur My Blog
    By: TwitterButtons.com
    Friday, January 21, 2011 5:19 PM
    Moderator
  • I am extracting data from a database in sql server to load into another database in a different sql server but same host. on OLE DB Source I use sql code to extract the data from the source database. but for me ot be able to get my desired data in one of the columns in the destination table, I have to create a derived column to be able to fill that column for me, hence the use of the case statement in derived column. I'm not using execute sql task as that is only done on the control flow.
    Friday, January 21, 2011 5:23 PM
  • Why not to

    1. Extract the data using Execute SQL Task with the correct SQL into a recordset;
    2. Then iterate over it and insert into the other target?

    Example (just in case) http://sqlblog.com/blogs/louis_davidson/archive/2007/01/19/ssis-foreach-looping-through-a-recordset.aspx


    Arthur My Blog
    By: TwitterButtons.com
    Friday, January 21, 2011 5:29 PM
    Moderator
  • Thanks Arthur for this link, it really helps, but how do I then connect the result to show on my destination table? The code is running on the execute sql task but how do I get the column to show in my destination table because that column does not exist in the source table that's why I was thinking of derived column. Is there anything I can do? Thanks
    Friday, January 21, 2011 5:48 PM
  • By running the appropriate query against the source you already get the new [non-existent] column created, now you need to insert this value to the target, will an insert SQL do?
    Arthur My Blog
    By: TwitterButtons.com
    Friday, January 21, 2011 5:56 PM
    Moderator
  • If case statement does not work in derive column, add one script component as a transformation just before derive column. Take current row from imput buffer, do if-else/case checking and add Active/ Inactive/ External/ Withdrawn as a row to output buffer. Then use it in derive column.

    Chandrajit Samanta
    Friday, January 21, 2011 8:33 PM
  • Hi Aamir, just to let you know, I followed your method but took out the table names and it worked. Thank you very much for the lead!!!
    Friday, January 21, 2011 9:16 PM