Evaluating an expression programmatically

Question

Basic question

Is there a method I'm missing to evaluate an expression within SSIS?

Backstory

I am programmatically fixing a slew of packages so that they conform to our standards. In the most simple form, assume I am creating a new variable and assigning an expression on it. This works fine.

If I click the ellipses and then click Evaluate Expression in the resulting dialog window, I see that my formula evaluates is syntactically correct and logically it's generating the value I expect. How does the evaluation happen?

When I click "OK" in the preceding step, notice the change to the .Value of my variable. It's now populated with the evaluated version of the expression. I need to simulate the same steps within my code but I'm at a loss of how it does it in BIDS/SSDT. Conceptually, it's just variable.Value = MAGIC; but I failed wizarding 101.

Why is this a problem

The real work I am performing is much more complicated than this. I'm adding and configuring several tasks as well as mucking with the data flow. One of those steps is a adding an Execute SQL Task that nukes the existing data out of the table, using a variable which has an expression assigned to it (what table to punt). The runtime of the package doesn't seem to evaluate the actual expression which ... results in me finding my issue.

Reproduction code

/// <summary>
/// Create a simple package with a variable that has an expression to figure
/// out how to evaulate the resulting value.
/// </summary>
static void ExpressionPOC()
{
    Microsoft.SqlServer.Dts.Runtime.Package p = new Microsoft.SqlServer.Dts.Runtime.Package();
    Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
    p.Name = "ProofOfConcept";
    Microsoft.SqlServer.Dts.Runtime.Variable v = null;
    // This creates a simple variable of type string with an initial value of blank string
    v = p.Variables.Add("ContrivedExample", false, "User", string.Empty);
    v.Expression = @"""My package is named "" + @[System::PackageName]";
    // Looking for something here to 
    //v.Value = v.Expression.EvaluatePrettyPlease();
    app.SaveToXml(@"C:\Users\bfellows\Documents\Visual Studio 2012\Projects\Demo\Demo\testVariable.dtsx", p, null);
}

Answer 1

Resolution

As I've already written up this question, I'm going to post it in the hopes of saving someone else trouble. Prior to SQL Server 2012 or if you aren't using BIDSHelper to create your expressions, there are 2 steps to using variables with expressions.

1. Assigning the actual expression.
2. The one I was overlooking is setting EvaluateAsExpression = true. The tools now automate setting that flag.

Revised code is simply

//v.Value = v.Expression.EvaluatePrettyPlease();
// The missing EvaluatePrettyPlease method is the property EvaluateAsExpression
// By setting this property, the expressions actually evaluate
// go figure
v.EvaluateAsExpression = true;