SSIS Expressions - EvaluateAsExpression Problem

Question

In a Data Flow, I have an Derived Column task. In the expression for one of the columns, I have the following expression:

[siteid] == "100" ? "1101" : [siteid] == "110" ? "1001" : [siteid] == "120" ? "2101" : [siteid] == "140" ? "1102" : [siteid] == "210" ? "2001" : [siteid] == "310" ? "3001" : [siteid]

This works just fine. However, I intend to reuse this in at least a dozen other places so I want to store this to a variable and use the variable in the Derived Column instead of the hard-coded expression. When I attempt to create a variable, using the expression above, I get a syntax error saying 'siteid' is not defined. I guess this makes sense because it isn't. But how can I get this the expression to work by using a variable? It seems like I need some sort of way to tell it that 'siteid' will be the column containing the data I want to apply the expression to.

Answer 1

You can't use column names in variable expressions. I'm afraid there is no way to easily "clone" this logic in SSIS.

I would suggest, however, that you don't attempt to clone hard-coded logic like this. Construct a reference table and use a Lookup component to fetch the value. That way, if your "case" statement ever changes, you only need to do one modification - you don't need to hunt down everywhere this logic gets used. And configuration of the Lookup is pretty drag and drop easy.