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

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

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

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

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

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

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

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.

Thanks Aamir but it's still not working. Still gives error

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

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

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

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

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

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

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.

Why not to Extract the data using Execute SQL Task with the correct SQL into a recordset; 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.aspxArthur My Blog

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

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!!!