Monday, 1 September 2014

XSD location hardcoded in XML source

Case
The path of the XSD file in the XML Source component is hardcoded, but the path on my production environment is different than my development environment. The XML source doesn't have expressions. How do I make this XSD path configurable?
XML Source Connection Manager Page
without Connection Managers



























Solution
Unfortunately the XML source component does lack some very basic functionalities like the use of a Connection Manager (although ironically it's called the Connection Manager page). The source can use variables as input, but there isn't such option for the XSD file.
XML Task can use Connection Managers


























1) Find Expressions
The XML Source Component doesn't support expressions, but the Data Flow Task itself does. Go to the properties of the Data Flow Task and locate the expressions and click on the ... button

Select Data Flow Task and press F4 to get properties


























2) Select Property
Locate the XMLSchemaDefinition property of your XML Source in the Property Expression Editor and add an expression on it by clicking on the ... button.
Property Expression Editor
















3) Add Expression
Now you can either replace its value by a variable or a parameter if you use SSIS 2012 and above.
Expression Builder
























That's it. Now you have a workaround for the absence of a real Connection Manager. An alternative could be to use a Script Component as XML source or an XML file with an inline XSD schema.

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.
Related Posts Plugin for WordPress, Blogger...