Hello, I have an sql CASE condition which I use in my select statement to generate a column.Now I want that condition to given in SSIS dervide column as a expression. I am unable to tranfer that CASE condition to a derived column transfer expression. Here is the condition , CASE WHEN EMPID = A and manager = B THEN CONVERT (DECIMAL(18,2),ROUND(SALARY1 -SALARY2) * BONUS,2)) + CONVERT(DECIMAL(18,2),ROUND(SALARY2) * (BONUS),2)) WHEN EMPID = B AND MANAGER = B THEN CONVERT (DECIMAL(18,2),ROUND(SALARY1 -SALARY2) * BONUS,2)) + CONVERT(DECIMAL(18,2),ROUND(SALARY2) * (BONUS),2)) END AS COMPANY The above query generates a column in a select statement.Now I want to generate a column in SSIS package using derived column transformation.How to write the above condition as an expression to generate a column in derived column tranformation. Please some one help .Its urgent. Thank you.

try this : ([EMPID]=="A" && [Manager]="B") : (DT_NUMERIC,18,2) (ROUND([SALARY1]-[SALARY2] * [BONUS] , 2)) + (DT_NUMERIC , 18,2) ( ROUND([SALARY2]*[BONUS],2) ) ? ( ([EMPID]=="B" && [Manager]="B") : (DT_NUMERIC,18,2) (ROUND([SALARY1]-[SALARY2] * [BONUS] , 2)) + (DT_NUMERIC , 18,2) ( ROUND([SALARY2]*[BONUS],2) ) ? 0 ) as I see in your case statement both cases results same!http://www.rad.pasfu.com

An alternative option could be a Script Component (type transformation). The Derived Column component can become a little unreadable for large if statements... (a multi-line expression box like in SSRS would be a little better)Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

One approach to developing and testing expressions is to use the standalone Expression Tester tool - http://expressioneditor.codeplex.com/ It doesn't support columns, but I mock up my columns by adding variables to mimic the columns. I then develop and test the expression, and finally use Find & Replace to change the variable names to the column names, and paste it into the Derived Column Editor. If it can be done in an exression I personally prefer to do so.http://www.sqlis.com | http://www.konesans.com

SSISJost, Can you show me how to write in script component for the above column to generate that in a scirpt transformation task. Thank you.

SSISJost, Can you show me how to write in script component for the above column to generate that in a scirpt transformation task. Thank you. No problem, but do you have C# or VB.net experience?Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Ya I can show what I have.Please correct me forthe above the condition Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim Error_Message As String Dim Value As String Value = ControlChars.CrLf Row.Company= CDec((0.0)) On Error GoTo Error_Process If Row.Manager = "B" Then Row.Company = CDec(RoundIt((salary1) * (Row.bonus), 2)) + CDec(RoundIt((Row.salary2 - Row.salary1) * Row.bonus, 2)) End If Exit Sub Error_Process: MsgBox(Error_Message, 0, Err.Description) End Sub Public Function RoundIt(ByVal aNumberToRound As Double, Optional ByVal aDecimalPlaces As Double = 0) As Double On Error GoTo ErrHandler Dim nFactor As Double Dim nTemp As Double nFactor = 10 ^ aDecimalPlaces nTemp = (aNumberToRound * nFactor) + 0.5 RoundIt = Int(CDec(nTemp)) / nFactor '-----------EXIT POINT------------------ ExitPoint: Exit Function '-----------ERROR HANDLER--------------- ErrHandler: Select Case Err.Number Case Else ' Your error handling here RoundIt = 0 Resume ExitPoint End Select End Function End Class

The if statement could look something like this: If (Row.EMPID = "A" AND Row.Manager = "B") Then ' Case 1.... ElseIf (Row.EMPID ="B" AND Row.Manager = "B") Then ' Case 2..... Else ' Optional case... End If But you could also use a Case instead of an IF-Statement: http://msdn.microsoft.com/en-us/library/cy37t14y(v=vs.80).aspx For rounding there are a lot of options: - Custom functions like your own - .ToString("0.00")) (bit dirty) - Math.Round(d) (see: http://msdn.microsoft.com/en-us/library/3s2d3xkk(v=VS.90).aspx) End I wouldn't use the messagebox (only for a quick debug). Here are some alternatives: http://microsoft-ssis.blogspot.com/2011/04/breakpoint-does-not-work-within-ssis.html and http://microsoft-ssis.blogspot.com/2011/02/script-task-and-component-logging.html Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com