Building the User Interface

Now that the key stages have been explained, you can examine each of them in detail. This guidance makes very few assumptions, explaining all the actions required; so as long as you can open Visual Studio on your own, you should be able to follow these steps, and perhaps more importantly understand why.

Adding the Project

If you followed the example in the previous chapter, you currently have an existing solution in Visual Studio 2005 that contains the pipeline component project (ReverseString). Therefore, your first step is to add a new Class Library project to host the UI, as shown in Figure 15-1. Although the UI can be implemented within the pipeline component project, for performance reasons this is not the recommended approach. Because SSIS has distinct runtime versus design-time elements, the combination of the two functions leads to a larger assembly, which requires more memory and consequently lower runtime performance. The separate design practice also allows for easier code development and maintenance, reducing confusion and conflicts within the areas of code.

Figure 15-1

Starting with the empty project, the first task is to configure any project properties, so you need to set the Assembly name and Default namespace to be consistent with your development practices, as shown in Figure 15-2.

Figure 15-2

The user interface assembly does not need to be placed in a defined location like tasks and components (%Program Files%\Microsoft SQL Server\90\DTS\PipelineComponents or %Program Files%\ Microsoft SQL Server\90\DTS\Tasks), but it does need to be installed within the global assembly cache (GAC). So within the project properties, you can leave the build output path location as the default value, but for ease of development you can add a post-build event command on the Build Events page, as shown in Figure 15-3.

Figure 15-3

Because the assembly will be installed in the GAC, you will need to sign the assembly using a strong name key, which can be configured from the Signing page, as shown in Figure 15-4. For more information about strong names and their importance in .Net, see "Security Briefs: Strong Names and Security in the .NET Framework" (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/strongNames.asp).

Figure 15-4

Although most assembly attributes can now be set through the Assembly Information dialog box accessed from the Application page of Project Properties, you still next need to manually edit AssemblyInfo.cs, adding the CLSCompliant attribute, as described in Chapter 14 and as shown here:

 #region Using directives using System; using System.Security.Permissions; using System.Reflection; using System.Runtime.CompilerServices; using System.Runtime.InteropServices; #endregion [assembly: AssemblyTitle("ReverseStringUI")] [assembly: AssemblyDescription("Reversing String Transformation UI for SQL Server Integration Services")] [assembly: AssemblyConfiguration("")] [assembly: AssemblyCompany("Konesans Ltd")] [assembly: AssemblyProduct("Reverse String Transformation")] [assembly: AssemblyCopyright("Copyright (c) 2005 Konesans Ltd")] [assembly: AssemblyTrademark("")] [assembly: AssemblyCulture("")] [assembly: AssemblyVersion("1.1.0.0")] [assembly: AssemblyFileVersion("1.1.0")] [assembly: CLSCompliant(true)] [assembly: PermissionSet(SecurityAction.RequestMinimum)] [assembly: ComVisible(false)] 

Note

The AssemblyVersion will form part of the UITypeName property described below; therefore it is important that this is not allowed to auto-increment using the * token, as this will break the linkage between the component and its user interface.

You also require a Windows Form to actually display your component's interface to the user in addition to the default class you have in your project, so one can be added at this stage.

The final preparatory task is to add some additional references to your project. The recommended three are listed here:

• Microsoft.SqlServer.Dts.Design

• Microsoft.SqlServer.DTSPipelineWrap

• Microsoft.SQLServer.ManagedDTS

Implementing IDtsComponentUI

You now have the empty framework for the UI assembly, and you can start coding. The first step is to implement the Microsoft.SqlServer.Dts.Pipeline.Design IDtsComponentUI interface. Using the default class in the project, you can add the interface declaration and take advantage of the new context menu features, as well as use the Implement Interface command to quickly generate the five method stubs, saving you from manually typing them out.

The methods are documented in detail in the following list; however, it is useful to understand the scenarios in which each method is called, highlighting how the Initialize method is usually called before the real action method:

• Adding a new component to the package:

  • Initialize

  • New

• Edit the component, through a double-click or by selecting Edit from the context menu:

  • Initialize

  • Edit

• Delete the component, through the Delete key or by selecting Delete from the context menu:

  • Delete

You will now look at the methods in more detail and examine how they are implemented in the example.

IDtsComponentUI.Delete

The Delete method is called when a component is deleted from the SSIS designer. It allows you to perform any cleaning operations that may be required or warn users of the consequences. This is not normally required, because the consequences should be fairly obvious, but the opportunity is available.

For this example, simply remove the placeholder exception, leaving an empty method.

IDtsComponentUI.Help

The Help method has not been implemented in SQL Server 2005. For this example, simply remove the placeholder exception. The method will not be called, but this should prevent any surprises in case of a service pack introducing the functionality, although this is unlikely.

IDtsComponentUI.New

The New method is called when a component is first added to your package through the SSIS designer. Use this to display a user interface specific to configuring the component for the first time, such as a wizard to help configure the component or an option dialog box that gathers some information that will influence the overall use of the component. The Script transformation uses this method to display a dialog box asking you to specify the type, source, destination, or transformation.

The New method is not widely used, because configuration of the component usually requires you to have wired up the data flow paths for the component. In addition, most people start by laying out the package and adding most or all of the components together, allowing them to visualize and validate their overall data flow design, before configuring each component in detail, but in specialized circumstances you have this option.

For this example, simply remove the placeholder exception, leaving an empty method.

IDtsComponentUI.Initialize

Initialize is the first method to be called when adding or editing a component, and although you do not actually perform any actions at this stage, the parameters provided are normally stored in private member variables for later use. At a minimum, you will store the IDTSComponentMetaData90 reference, because a UI will always need to interact with the underlying component, and this is done through the IDTSComponentMetaData90 reference.

For components that use connections or variables, you would also store a reference to IServiceProvider. This allows you to access useful services like the connection service (IDtsConnectionService) and the variable service (IDtsVariableService). These designer services allow you to create new connections and variables, respectively. For connections, the service will invoke the Connection Manager user interface, provided by the connection author, and for variables you use the dialog box built into the SSIS designer. This is a good example of how Microsoft has made life easier for component developers, offering access to these services, saving you time and effort. There are two other services available, the IErrorCollectionService for retrieving error and warning event messages, and IDtsClipboardService, which allows component developers to determine if a component was created by a copy-and-paste operation.

In the Reverse String example, these services are not required, but you would follow the same pattern as you do with IDTSComponentMetaData90 here.

 private IDTSComponentMetaData90 _dtsComponentMetaData; [CLSCompliant(false)] public void Initialize(IDTSComponentMetaData90 dtsComponentMetadata, IServiceProvider serviceProvider) { // Store ComponentMetaData for later use _dtsComponentMetaData = dtsComponentMetadata; } 

IDtsComponentUI.Edit

The Edit method is called by the designer when you edit the component, and this is the place where you actually display the visible window or form of the user interface component. The purpose of the Edit method is to display the form, passing through any references you need, stored in private variables during Initialize. The Edit method also has a Boolean return value that notifies the designer whether changes have been made.

This is perhaps one of the most useful features of the component UI pattern, as it allows you to make changes directly to the component, but they are persisted only if the return value is true. You get commit or rollback functionality for free, rather than having to write additional code to cache changes within the UI and only apply them when a user clicks the OK button. It also allows you to benefit from validation routines you have written into the component itself. For example, the ReverseString.SetUsageType method checks data types and the UsageType property for the column being selected, since this component supports only string types. Putting the validation into the component, rather than the UI, ensures that if a user bypasses your UI and uses the built-in Advanced Editor or the Visual Studio Properties instead, the same validation takes place.

Therefore, your UI should focus on the display side and leave as much validation as possible to the component. Inevitably, some validation will be implemented in the UI, but always bear in mind that you can use the existing component code in this way, saving time and simplifying maintenance through this reuse.

For ease of implementation, you can use the DialogResult functionality of the form to indicate the return value for the form. This is illustrated in the example implementation of Edit:

 public bool Edit(IWin32Window parentWindow, Variables variables, Connections connections) {    try    {       // Create UI form and display       ReverseStringUIForm ui = new ReverseStringUIForm(_dtsComponentMetaData);       DialogResult result = ui.ShowDialog(parentWindow);       // Set return value to represent DialogResult. This tells the    // managed wrapper to persist any changes made       // on the component input and/or output, or properties.       if (result == DialogResult.OK)       {          return true;       }    }    catch (Exception ex)    {       MessageBox.Show(ex.ToString());    }    return false; } 

The Edit method also provides references to the Variables and Connections collections. You can use these collections to list the available variables and connections. The Variables collection is already limited to those in scope for the current Data Flow task.

If your component uses connections or variables, you would modify the form constructor to accept these, as well as the System.IServiceProvider reference you captured during Initialize. This allows you to offer the option of selecting an existing item or creating a new one as required. These are not required for the Reverse String component, but an example of an Edit method implementation using them is shown here:

 public bool Edit(IWin32Window parentWindow, Variables variables, Connections connections) {    try {       TraceSourceUIForm ui = new TraceSourceUIForm(_dtsComponentMetaData,          variables, connections, _serviceProvider);       DialogResult result = ui.ShowDialog(parentWindow);       if (result == DialogResult.OK)       {          return true;       }    }    catch (Exception ex)    {       Konesans.Dts.Design.ExceptionDialog.Show(ex);    }    return false; } 

Setting the UITypeName

This section deals with changes to the Reverse String component itself, rather than the user interface project. This is listed as the last of the three key steps for providing a user interface, but it is generally done fairly early on, because once it's complete, you can actually test your UI in the designer itself.

You need to tell the designer that your component has a user interface, in effect overriding the Advanced Editor dialog box provided by default. To do this, set the UITypeName property of the DtsPipelineComponentAttribute, which already decorates the component class in the transformation project. The required format of the property value is as follows:

 <Full Class Name>, <Assembly Name>, Version=<Version>, Culture=Neutral, PublicKeyToken=<Public Key Token> 

Note

You may recognize the format as being very similar to an assembly strong name, because apart from the additional <Full Class Name> at the beginning, it is the assembly strong name. Using the strong name, the designer can find and load the assembly, and then using the class name, it knows exactly where to go for its entry point, the IDTSComponentUI implementation.

Setting this property often causes people problems, but if you know where to look, it is quite easy.

 ... namespace Konesans.Dts.Pipeline.ReverseStringUI {    public class ReverseStringUI : IDtsComponentUI    { ... 

This code snippet from the main UI class file shows the namespace and the class name, so the first token on the UITypeName is Konesans.Dts.Pipeline.ReverseStringUI.ReverseStringUI.

The remainder is just the strong name of the assembly. The simplest way to obtain this is to compile the project, and if you set the post-build events as described above, your assembly will have been installed in the GAC. Open the assembly viewer (C:\WINDOWS\assembly) and locate your assembly. The tooltip for an assembly will show the string name, as shown in Figure 15-5.

Figure 15-5

The individual tokens are shown again in the Properties dialog box, and there you can highlight the text for copy-and-paste operations to save typing mistakes, particularly with the public key token.

If you make a mistake in setting this property, you will get an error such as this one when you to use the component UI.

 Could not load file or assembly 'Konesans.Dts.Pipeline.TrashDestination, Version=1.0.1.0, Culture=neutral, PublicKeyToken=b8351fe7752642cc' or one of its dependencies. The system cannot find the file specified. (mscorlib) 

The completed attribute for the ReverseString component, referencing the ReverseStringUI assembly, is illustrated as follows:

 [DtsPipelineComponent(    DisplayName = "ReverseString",    ComponentType = ComponentType.Transform,    IconResource = "Konesans.Dts.Pipeline.ReverseString.ReverseString.ico",    UITypeName = "Konesans.Dts.Pipeline.ReverseStringUI.ReverseStringUI,    Konesans.Dts.Pipeline.ReverseStringUI, Version=1.1.0.0, Culture=neutral, PublicKeyToken=7b20fe705a17bed2")] public class ReverseString : PipelineComponent ... 

Building the Form

The final stage of the development is to build the form itself, allowing it to capture the user input and apply the selections to the component. You are about to start building the form, but before you do, review the following summary of the progress so far.

You have implemented IDTSComponentUI, providing the methods required by the designer to support a custom user interface. The IDTSComponentUI.Edit method is used to display the form, passing through a reference to the base component (IDTSComponentMetaData90). This was gained initially through the IDTSComponentUI.Initialize method and stored in a private class-level variable.

Finally, you have updated the component itself to include the UITypeName property for the DtsPipelineComponentAttriute. This allows the designer to detect and then find your user interface class, thereby calling the IDTSComponentUI methods you have now implemented, leading to the display of the form.

The sample form for the user interface is shown in Figure 15-6.

Figure 15-6

Form Constructor

As previously mentioned, the default form constructor is modified to accept the references you will need, such as the component and support objects, variables, and connections. For this example, you just have the component, IDTSComponentMetaData90. You should store these constructor parameters in private member variables for later use elsewhere in the form, as well as using them directly in the constructor itself.

The commit and rollback feature discussed above in the "IDtsComponentUI.Edit" section has one specific requirement. Any changes made must be done through a wrapper class, rather than applied directly to the IDTSComponentMetaData90 reference. This wrapper, the CManagedComponentWrapper design-time interface, is created within the constructor and stored in a private member variable for later use.

Changes can be made directly to IDTSComponentMetaData90, but they will be permanent, so even if you return false from IDtsComponentUI.Edit, the changes will persist. Users like recognizable and intuitive user interfaces, and the ability to recover from a mistake with the Cancel button is one of those design patterns that all users have been grateful for on numerous occasions. Writing code to implement this yourself would be a considerable amount of work, so make sure you issue changes only through the design-time interface.

The complete form constructor is shown as follows, including the call to the SetInputVirtualInputColumns method, covered later in the chapter:

 private IDTSComponentMetaData90 _dtsComponentMetaData; private CManagedComponentWrapper _designTimeComponent; private IDTSInput90 _input; public ReverseStringUIForm(IDTSComponentMetaData90 dtsComponentMetaData) {    InitializeComponent();    // Store constructor parameters for later    _dtsComponentMetaData = dtsComponentMetaData;    // Get design-time interface for changes and validation    _designTimeComponent = _dtsComponentMetaData.Instantiate();    // Get Input    _input = _dtsComponentMetaData.InputCollection[0];    // Set any form controls that host component properties or connections here    // None required for ReverseString component    // Populate DataGridView with columns    SetInputVirtualInputColumns(); } 

Column Display

Once all of the constructor parameters have been stored and the initial preparation is complete, you can begin to interrogate the component and other objects that may have been supplied on the constructor to populate the form controls.

The Reverse String transformation will operate on any column the user selects, so the user interface will simply consist of a way to allow columns to be selected. For this example, you should use a DataGridView control. Using the control designer, you'll preconfigure two columns, a checkbox column for the selection state (DataGridViewCheckBoxColumn) and a text column for the column name (DataGridViewTextBoxColumn). The individual form controls will not be covered in detail; rather the focus will be on their use and interaction with the component, as the choice of control is entirely up to you as the user interface developer. To see exactly how the controls have been configured, review the completed project available at www.wrox.com.

Because you allow users to select columns, the initial requirement is to enumerate the columns and determine their current selection state. To find out how to do this, you need to understand the architecture of a component in relation to data movement. For a simple synchronous transformation such as this one, you have a single input. The input has a collection of input columns, which at runtime hold the data provided in the pipeline buffer, so the transformation itself operates on these columns.

Note

For more detail on pipeline architecture, see Chapter 10.

In the Reverse String component, the presence of an input column means that the user wants the operation to be performed on that column. By default, the input will contain no columns, because no columns have been selected for transformation. To select a column, you set the column usage type to something other than DTSUsageType.UT_IGNORED. For this component, because you do an in-place transformation on the column value, you require both read and write access as indicated by DTSUsageType.UT_READWRITE. This allows you to read the column value and reverse it before writing it back into the buffer.

Note

It is important that you select only columns that are required for any transformation and minimize excess columns through all stages of the pipeline for performance reasons. The designer will display a warning like this when it detects unused columns:

 [DTS.Pipeline] Warning: The output column "ProductPrice" (36) on output "OLE DB Source Output" (10) and component "Products" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance. 

Because the input column collection is empty by default, you actually work on the virtual input column collection instead. The virtual input represents all the upstream columns available to the transformation, allowing you to enumerate columns, as well as interrogating the virtual input column's UsageType property.

Calling GetVirtualInput to get the collection of virtual columns is a potentially expensive operation, depending on the number of upstream columns. You should therefore cache the result for later use in other methods. You should also be aware that since a virtual input is very much a snapshot of current state, it can become invalid. Simple changes to the current component do not affect the virtual columns, but deeper changes like ReinitializeMetaData can invalidate it. You should therefore plan the lifetime of the cached reference and periodically refresh it after major changes.

The use of the virtual input and the column usage type is the basis for the SetInputVirtualInputColumns helper method included in the form. This populates the DataGridView with a list of columns and their current selection state. This method is the final call in the form constructor and completes the initialization of the form.

 private void SetInputVirtualInputColumns() {    _virtualInput = _input.GetVirtualInput();    IDTSVirtualInputColumnCollection90 virtualInputColumnCollection =       _virtualInput.VirtualInputColumnCollection;    IDTSInputColumnCollection90 inputColumns = _input.InputColumnCollection; int columnCount = virtualInputColumnCollection.Count;    for (int i = 0; i < columnCount; i++)    {       IDTSVirtualInputColumn90 virtualColumn = virtualInputColumnCollection[i];       int row;       if (virtualColumn.UsageType == DTSUsageType.UT_READONLY ||          virtualColumn.UsageType == DTSUsageType.UT_READWRITE)       {          row = this.dgColumns.Rows.Add(   new object[]             { CheckState.Checked, " " + virtualColumn.Name });       }       else       {          row = this.dgColumns.Rows.Add(new object[]             { CheckState.Unchecked, " " + virtualColumn.Name });       }       this.dgColumns.Rows[rowIndex].Tag = i;       DataGridViewCheckBoxCell cell =            (DataGridViewCheckBoxCell)dgColumns.Rows[row].Cells[0];       cell.ThreeState = false;    } } 

The pipeline engine is implemented in native code for performance, so calls to pipeline objects normally use a wrapper class and incur the overhead of COM Interop. You should therefore minimize such calls through efficient coding practices. In the preceding example, the count from the virtual input column collection is retrieved only once, as opposed to being interrogated within the for loop test itself.

Column Selection

The next stage of the user interface is to react to user input and reflect any changes back to the component. In this example, the only choice offered is the selection of columns, made through the DataGridView, as captured through the CellContentClick event. You use this event rather than one of the others available such as CellValueChanged, as this is raised immediately and you can give timely feedback to the user.

Through the DataGridViewCellEventArgs, you can obtain the row and column indices for the cell. This is first used to validate that the row exists and that the column is the first column, because this column contains the checkboxes used for managing selection. You then use the virtual input again and set the usage type as indicated by the checkbox or cell value.

Since the example component includes validation within the overridden SetUsageType method, you need to ensure that you catch any exceptions thrown and can react and feedback to the component user as shown here:

 private void dgColumns_CellContentClick(object sender, DataGridViewCellEventArgs e) {    if (e.ColumnIndex == 0 && e.RowIndex >= 0)    {       // Get current value and flip boolean to get new value       bool newValue = !Convert.ToBoolean(dgColumns.CurrentCell.Value);       // Get the virtual column to work with       IDTSVirtualInputColumn90 virtualColumn =          _virtualInput.VirtualInputColumnCollection[e.RowIndex];       try       {          // Set the column UsageType to indicate the column is selected or not          if (newValue)             _designTimeComponent.SetUsageType(_input.ID, _virtualInput,                virtualColumn.LineageID, DTSUsageType.UT_READWRITE);          else             _designTimeComponent.SetUsageType(_input.ID, _virtualInput,                virtualColumn.LineageID, DTSUsageType.UT_IGNORED);       }       catch(Exception ex)       {          // Catch any error from base class SetUsageType here.          // Display simple error message from exception          MessageBox.Show(ex.Message, "Invalid Column", MessageBoxButtons.OK,             MessageBoxIcon.Error);          // Rollback UI selection          dgColumns.CancelEdit();       }    } } 

To complete the description of the user interface example, there are two button controls on the form, named OK and Cancel, each with their respective DialogResult property values set. By using the dialog results in this way, you do not need any event handler bound to the click event and no additional code is required to close the form. The dialog result is then used within IDTSComponentUI.Edit to commit or roll back any changes made to the component wrapper, as shown previously.

This concludes the example, and if you have been building as you read, all that remains is to compile the project. If you configured the build events that were described at the beginning, the assemblies should be in the correct locations ready for use.

You will need to start a new instance of Visual Studio and open a SSIS project. Before you can use the component, it needs to be added to the Toolbox. To add a component to the Toolbox, right-click on the Toolbox and select Choose Items from the context menu. When the Choose Toolbox Items dialog appears, click the SSIS Data Flow Items tab and scroll down until you see the component. Check your new component and click OK. When you go back to the Toolbox, you should see your new component. Another method is to select Reset Toolbox from the context menu instead.

The completed example is available for download from www.wrox.com.