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.

Tuesday, 1 July 2014

Prevent events executing multiple times

Case
I have a log task in an OnPreExecute and OnPostExecute event handler, but it executes multiple times. Why is that and how can I prevent it?

Solution
This is because all tasks and containers fire events and these events are propagated to their parent container and then to their parent container and so on. This means that if you have a package with a Sequence Container with and with an Execute SQL Task in it, that each of them fires events. Let's test that.

For testing purposes I added an Execute SQL Task in each event handler with an insert query to show which events are fired. Each task inserts the name of the event and the value of the System Variable SourceName in a log table with an identity column LogId.
Log all events, executable is package



















When you run the package you can see that for example the OnPreExecute event has three records:
1) the Package
2) the Sequence Container
3) the Execute SQL Task
Event handlers executing multiple times



















The trick
The trick to execute the Execute SQL Task in the event handler(s) only once, is to check whether the source of the event is the package and not one of it's children (containers/tasks).

Add a dummy Script Task or an empty Sequence Container in front of the Execute SQL Task and add a Precedence Constrain expression between them: @[System::SourceName] ==  @[System::PackageName]
Expression to filter events that are not from the package




















Related Posts Plugin for WordPress, Blogger...