Building the User Interface - SSIS

Now that the key stages have been explained, you can examine each of them in detail. This guide makes very few assumptions about your current level of development experience, 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 important, understand why you are performing each one.

Adding the Project

If you followed the example in the previous Programming and Extending SSIS Topic , you currently have an existing solution in Visual Studio that contains the Pipeline Component project (Wrox.Pipeline). Therefore, your first step is to add a new Class Library project to host the UI, as shown in below screen shot. 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, combining the two functions leads to a larger assembly, which requires more memory and consequently results in lower runtime performance. When you deploy your components in production, the component UI is never shown, so it is important that your components can operate without a UI. To support this use, you should ensure the core component code does not have any dependencies on UI code. The practice of separating the UI and component projects allows for easier code development and maintenance, reducing confusion and conflicts within the areas of code.

As you start 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 below screen shot.

The user interface assembly does not need to be placed in a defined location like components and tasks (%Program Files%Microsoft SQL Server120DTSPipelineComponents or %Program Files%Microsoft SQL Server120DTSTasks, respectively), but it does need to be installed within the global assembly cache (GAC). Therefore, 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 below screen shot. Refer to Programming and Extending SSIS Topic  for more details on what this command should look like.

Because the assembly will be installed in the GAC, you need to sign the assembly using a strong name key, which can be configured from the Signing page, shown in below screen shot. 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/EN-US/LIBRARY/AA302416.ASPX

Although most assembly attributes can now be set through the Assembly Information dialog, accessed from the Application page of Project Properties, you still need to manually edit AssemblyInfo.cs (AssemblyInfo.cs file of the Wrox.Pipeline.UI project), which can be seen by clicking on the arrow next to Properties in the Solution Explorer, adding the CLSCompliant attribute, as described in Programming and Extending SSIS Topic and shown here:

NOTE The AssemblyVersion will form part of the UITypeName property, described later in this Adding a User Interface to Your Component Topic; therefore, it is important that this is not allowed to auto-increment using the ∗ token, because this will break the linkage between the component and its user interface.

You also need 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 will be added at this stage.

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

• Microsoft.SqlServer.Dts.Design
• Microsoft.SqlServer.DTSPipelineWrap
• Microsoft.SQLServer.ManagedDTS
• System.Windows.Forms
• Microsoft.SqlServer.DTSRuntimeWrap

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 Visual Studio 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 sections; 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

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

                        Initialize
                        Edit

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

                       Delete

The following sections describe each method in more detail and demonstrate how they are implemented in the example.

IDtsComponentUI.Delete

The Delete method is called when a component is deleted from the SSIS designer. It enables 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 2014. 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 method 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 that gathers some information that will influence the overall use of the component. The Script Transformation uses this method to display a dialog asking for 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, enabling 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; 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 IDTSComponentMetaData100 reference, because a UI always needs to interact with the underlying component, and this is done through the IDTSComponentMetaData100 reference.

For components that use connections or variables, you would also store a reference to IServiceProvider. This enables you to access useful services, like the connection service (IDtsConnectionService) and the variable service (IDtsVariableService). These designer services enable you to create new connections and variables, respectively. For connections, the service will invoke the Connection Manager user interface, provided by the connection author; for variables, you use the dialog 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. Two other services are available: IErrorCollectionService for retrieving error and warning event messages, and IDtsClipboardService, which enables component developers to determine whether a component was created by a copy-and-paste operation.

In the Wrox.Pipeline example, these services are not required, but you would follow the same pattern as you do with IDTSComponentMetaData100 here (ReverseStringUI.cs file of the Wrox.Pipeline.UI project):

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 enables you to make changes directly to the component, but they are persisted only if the return value is true. In other words, users can make as many changes as they want in the custom UI, but none of those changes are saved into the component unless the return value is true. You get commit or rollback functionality free, rather than having to write additional code to cache changes within the UI, and apply them only when a user clicks the OK button.

It also enables 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, because 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 a modularized manner, saving time and simplifying maintenance through 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 (ReverseStringUI.cs file of the Wrox.Pipeline.UI project):

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 is 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 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:

NOTE You may recognize the format as being very similar to an assembly strong name, because apart from the additional  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 developers problems, but if you know where to look, it is quite easy 

…
namespace Wrox.Pipeline.UI
{
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 Wrox.Pipeline.UI.ReverseStringUI.

The remainder is just the strong name of the assembly. The simplest way to obtain this is to compile the project; if you set the post-build events as described previously, your assembly will have been installed in the GAC. Open a command window and run the command gacutil /l Wrox.Pipeline.UI to get the full string name of the assembly. Figure 20-5 shows the full path on my machine.

The GACUTIL executable should be available in the Toolsdirectory.

If the gacutil call fails, make sure you change directory (CD) into the NETFX 4.5.1 Tools directory that contains the gacutil executable. Now that you can see the PublicKeyToken property, the UITypeName property can be set.

If you make a mistake when setting this property, you will get an error such as the following when you use the component UI:

Could not load file or assembly ‘Wrox.Pipeline.UI.ReverseStringUI, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b8351fe7752642cc’ or one of its dependencies. The system cannot find the file specified. (mscorlib)

The completed attribute for the Wrox.Pipeline Component, referencing the ReverseStringUI assembly, is illustrated as follows (ReverseString.cs file of the Wrox.Pipeline.UI project):

Building the Form

The final development stage is building the form itself, enabling it to capture the user input and apply the selections to the component. Before you start building the form, however, 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 (IDTSComponentMetaData100). This was gained using 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 DtsPipelineComponentAttribute. This enables 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 in below screen shot

Modifying the 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 reference, IDTSComponentMetaData100. You should store these constructor parameters in private member variables for later use elsewhere in the form, as well as use the member variables directly in the constructor itself.

The commit and rollback feature discussed 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 IDTSComponentMetaData100 reference. This wrapper, the IDTSDesigntimeComponent100 design-time interface, is created within the constructor and stored in a private member variable for later use.

Changes can be made directly to IDTSComponentMetaData100, 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 capability to recover from a mistake using the Cancel button is a design pattern 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 (ReverseStringUIForm.cs file of the Wrox.Pipeline.UI project), including the call to the SetInputVirtualInputColumns method, covered later in the Further Post:

Column Display

Once all 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 are not covered in detail; rather, the focus is on their use and interaction with the component, because the choice of control is entirely up to you as the user interface developer.

Frequently Asked SSIS Interview Questions & Answers

Because you allow users to select columns, the initial requirement is to enumerate the columns and determine their current selection state. 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.

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 contains 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 those 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:

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, enabling you to enumerate columns, as well as interrogate 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 call it only once and cache the result for later use in other methods. You should also be aware that because 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 (ReverseStringUIForm.cs file of the Wrox.Pipeline.UI project). As a separate exercise you may wish to augment this procedure with logic to hide (or gray-out) nonstring columns, so that users do not inadvertently try to reverse numeric values.

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 task to complete in building the user interface is to react to user input and reflect any changes back to the component. In this example, the only option 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, because 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.

Because the example component includes validation within the overridden SetUsageType method, you need to ensure that you catch any exceptions thrown and can react and provide feedback to the component user, as shown here (ReverseStringUIForm.cs file of the Wrox.Pipeline.UI project):

To complete the description of the user interface example, there are two button controls on the form, OK and Cancel, each with its 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; if you have been building the UI as you read, the only remaining task 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 need to start a new instance of Visual Studio and open an SSIS project. Before you can use the component, you need to verify the new components are part of the SSIS Toolbox tab. The components are added automatically after they are copied to the DTS directories, as explained in the “Installing the Components” section of Programming and Extending SSIS Topic. If the components are not present in the SSIS Toolbox, this is usually a sign that the assemblies have not been copied to those directories.

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator