Home » SQL ServerRSS

CASE statement in SSIS

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

 

16 Answers Found

 

Answer 1

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

 

Answer 2

The derived column transformation does not accept SQL statements. It uses Expressions.

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

 

 

Answer 3

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

 

Answer 4

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"))

)

 

Answer 5

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.
 

Answer 6

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

Answer 7

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

 

Answer 8

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
 

Answer 9

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.

 

Answer 10

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

 

Answer 11

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 

 

Answer 12

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?
 

Answer 13

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.
 

Answer 14

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.aspx

 

Answer 15

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
 

Answer 16

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?
 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter