Friday, 1 August 2014

Add Expression Builder to custom task

Case
I have created a custom task, but I would like to add the built-in Expression Builder to it. How do you do that?

Solution
It is possible, but it's an unsupported feature, which means you have no guarantees that it will still work after the next update of SSIS. For this example I used my Custom Task example and added two references and a couple of code lines. You can download that code and add the code below to the UI project.
Custom Task with built-in Expression Builder















1) References
For the Expression Builder window you need to add a reference to Microsoft.DataTransformationServices.Controls which is located in the GAC:
C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.DataTransformationServices.Controls\v4.0_11.0.0.0__89845dcd8080cc91\Microsoft.DataTransformationServices.Controls.DLL
And for validating the expression you need a reference to Microsoft.SqlServer.DTSRuntimeWrap which is also located in the GAC (however not in MSIL):
C:\Windows\Microsoft.NET\assembly\GAC_32\Microsoft.SqlServer.DTSRuntimeWrap\v4.0_11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.DTSRuntimeWrap.dll
 C:\Windows\Microsoft.NET\assembly\GAC_64\Microsoft.SqlServer.DTSRuntimeWrap\v4.0_11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.DTSRuntimeWrap.dll


2) Usings
I added two extra usings for the Expression Builder.

Two extra usings


















3) Controls
I added a button (for opening expression builder) a readonly textbox (for showing the expression) and a label (for showing the evaluated expression) to my editor.
Extra controls in the editor








4) The code
I added an onclick event on my button and added the following code (simplified version).
// C# code
private void btnExpression_Click(object sender, EventArgs e)
{
 try
 {
  // Create an expression builder popup and make sure the expressions can be evaluated as a string:
  // Or change it if you want boolean to System.Boolean, etc. Last property is the textbox containing
  // the expression that you want to edit.
  using (var expressionBuilder = ExpressionBuilder.Instantiate(_taskHost.Variables,
                 _taskHost.VariableDispenser,
                 Type.GetType("System.String"),
                 txtExpression.Text))
  {
   // Open the window / dialog with expression builder
   if (expressionBuilder.ShowDialog() == DialogResult.OK)
   {
    // If pressed OK then get the created expression
    // and put it in a textbox.
    txtExpression.Text = expressionBuilder.Expression;
    lblExpressionEvaluated.Text = "";

    // Create object to evaluate the expression
    Wrapper.ExpressionEvaluator evalutor = new Wrapper.ExpressionEvaluator();

    // Add the expression
    evalutor.Expression = txtExpression.Text;

    // Object for storing the evaluated expression
    object result = null;

    try
    {
     // Evalute the expression and store it in the result object
     evalutor.Evaluate(DtsConvert.GetExtendedInterface(_taskHost.VariableDispenser), out result, false);
    }
    catch (Exception ex)
    {
     // Store error message in label
     // Perhaps a little useless in this example because the expression builder window
     // already validated the expression. But you could also make the textbox readable
     // and change the expression there (without opening the expression builder window)
     lblExpressionEvaluated.Text = ex.Message;
    }

    // If the Expression contains some error, the "result" will be <null>.
    if (result != null)
    {
     // Add evaluated expression to label
     lblExpressionEvaluated.Text = result.ToString();
    }
   }
  }
 }
 catch (Exception ex)
 {
  MessageBox.Show(ex.Message);
 }
}

5) Runtime
Now you can store that expression in a property and retrieve in on runtime. On runtime you can evaluate the expression with the same code as above.