Extending the user interface - SSIS

  • (4.0)

The simple component that was used in the preceding example lacks some of features you may require in your project. For example, components can use runtime connections or have properties. These would generally be represented through additional form controls, and their values would be interrogated, and controls initialized in the form constructor. You will now look at these other methods in greater detail.

Learn how to use SSIS, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free SSIS Training Demo!

Runtime Connections

As previously discussed, components can use connections and the System.IServiceProvider from IDtsComponentUI.Initialize and the Connections collection from IDtsComponentUI.Edit enable you to provide meaningful UI functions around them. You have seen examples of passing these as far as the form constructor, so now you will be shown what you do with them. This example shows a modified constructor that accepts the additional connection-related parameters, performs some basic initialization, and stores them for later use. You would perform any column- or propertyrelated work as shown in the previous examples, but for clarity none are included here. The final task is to initialize the combo box that will list the runtime connections.

For this example, you will presume that the component accepts one connection, which would have been defined in the ProvidedComponentProperties method of the component. You will use a ComboBox control to offer the selection options, as well as the capability to create a new connection through the IDtsConnectionService. The component expects an ADO.NET SqlClient connection, so the list will be restricted to this, and the current connection, if any, will be preselected in the list. The preparatory work for this is all shown here (ReverseStringUIForm.cs file of the Wrox.Pipeline.UI project):

Runtime Connections

The final command in the constructor is to call your helper function, PopulateConnectionsCombo, to populate the combo box. The parameters for this are quite simple: the combo box to populate, the type of connection you wish to list, and the name of the currently selected connection. Using these three items, you can successfully populate the combo box as shown here (ReverseStringUIForm.cs file of the Wrox.Pipeline.UI project):

The ADO.NET connection is slightly different from most connections in that it has what can be thought of as subtypes. Because you need a specific subtype, the System.Data.SqlClient.SqlConnection, you must use the full name of the connection, as opposed to the shorter creation name moniker, ADO.NET, which you may see elsewhere and which is the pattern used for other simpler types of Connection Managers.

Perfect guide for getting started to applied SSIS. Access to freeSSIS Tutorials

If you have any problems with this sample code, perhaps because you have different versions of SQL Server on the same box, then change the relevant line of the preceding code to the following. This alternative code lists any Connection Manager in the combo box (ReverseStringUIForm.cs file of the Wrox.Pipeline.UI project):

Now that you have populated the combo box, you need to handle the selection of an existing connection or the creation of a new connection. When you author a Connection Manager yourself, you can provide a user interface by implementing the IDtsConnectionManagerUI, which is analogous to the way you have implemented IDtsComponentUI to provide a user interface for your component. The connection service will then display this user interface when you call the CreateConnection method.

The following example (ReverseStringUIForm.cs file of the Wrox.Pipeline.UI project) shows the event handler for the connections combo box, which supports new connections and existing connections and ensures that the selection is passed down to the component:

By following the examples shown here, you can manage connections from within your user interface, allowing the user to create a new connection or select an existing one, and ensure that the selection is persisted through to the component’s RuntimeConnectionCollection, thereby setting the connection.

You can also use variables within your UI. Normally, the selected variable is stored in a component property, so by combining the property access code from the component properties section and following the pattern for runtime connections, substituting the IDtsVariableService instead, you can see how this can be done.

Component Properties

As an example of displaying and setting component-level properties, you may have a string property that is displayed in a simple TextBox control and an enumeration value that is used to set the selected index for a ComboBox control. The following example assumes that the two component properties, StringProp and EnumProp, have been defined in the overridden ProvideComponentProperties method of your component class. You would then extend the form constructor to include some code to retrieve the property values and display them in the form controls. This assumes that you have added two new form controls, a TextBox control called MyStringTextBox, and a ComboBox called MyEnumValComboBox. An example of the additional form constructor code is shown here (ReverseStringUIForm.cs file of the Wrox.Pipeline.UI project):


The appropriate events for each control would then be used to set the property value of the component, ensuring that this is done through the design-time interface. A variety of events could be used to capture the value change within the Windows Form control, depending on the level of validation you wish to apply within the form or whether you wish to rely solely on validation routines within an overridden SetComponentProperty method in your component class. Capturing these within the control’s validating event would then enable you to cancel the change in the form, as well as display information to the user. A simple example is shown here for the two properties (ReverseStringUIForm.cs file of the Wrox.Pipeline.UI project):


Providing an overridden SetComponentProperty is a common requirement. The most obvious reason is that component properties are stored through the object type, but you may require a specific type, such as integer, so the type validation code would be included in SetComponentProperty. A simple example of this is shown here (ReverseStringUIForm.cs file of the Wrox.Pipeline.UI project), where the property named IntProp is validated to ensure that it is an integer:


In the next section, you build on this example by learning how to handle the exceptions and events.

Handling Errors and Warnings

The previous example and the column selection method in the main example both demonstrated how you can catch exceptions thrown from the base component when you apply settings. Although it is recommended that you use managed exceptions for this type of validation and feedback, you may also wish to use the component events such as FireError or FireWarning. In Programming and Extending SSIS Topic you saw an example of the FireError method in the Validation function. The example below gives an overview of how the FireError method is called. Usually, these would be called immediately prior to the exception and used to provide additional information in support of the exception. Alternatively, you could use them to provide the detail and throw the exception only as a means of indicating that an event has been raised. To capture the event information, you can use the IErrorCollectionService. This service can be obtained through System.IServiceProvider, and the preparatory handling is identical to that of IDtsConnectionService, as illustrated in the previous example. For the following examples, assume that a class-level variable containing the IErrorCollectionService has been declared, _errorCollectionService, and populated in the form constructor.

In the example above, ERROR_NUMBER references an error number if one is available. HELP_FILE_INDEX references the index of where the help topic for this error can be found in the help file. CANCEL_EXECUTION is a Boolean value that can be used to tell the SSIS Engine to stop execution of the package.

Frequently Asked SSIS Interview Questions & Answers

The following example (ReverseStringUIForm.cs file of the Wrox.Pipeline.UI project) demonstrates how you can use the GetErrorMessage method of the IErrorCollectionService to retrieve details about an event. This will also include details about any exception thrown. The validating method of a TextBox control is illustrated, and SetComponentProperty is based on the overridden example shown previously, to validate that the property value is an integer:

Error at Data Flow Task [ReverseString]: The IntProp property is required to be a
valid integer.
Error at Data Flow Task [ReverseString [84]]:
System.ArgumentException: The value
you have specified for IntProp is not a numeric value at Wrox.Pipeline.ReverseString.ReverseString.SetComponentProperty(String
propertyName, Object propertyValue) at
IDTSDesigntimeComponent100 wrapper, String propertyName, Object propertyValue)

The following example (ReverseStringUIForm.cs file of the Wrox.Pipeline.UI project) demonstrates the GetErrors method and how to enumerate through the errors captured by the service individually:


As you can see, both the event and the exception information are available through the IErrorCollectionService. This example also demonstrates the use of the Level property, which can be useful for differentiating between errors and warnings. For a complete list of IComponentErrorInfo properties, please refer to the SQL Server documentation.

Column Properties

When you require column-level information, beyond the selection state of a column, it is a best practice to store this as a custom property on the column. This applies to all column types. An example of this can be seen with the stock Character Map Transformation. If you select a column and perform an in-place operation, such as the Lowercase operation, this is stored as a custom property on that input column. To confirm this, select a column as described and view the component through the Advanced Editor (to open the Advanced Editor, right-click the Character Map Transformation and select Show Advanced Editor). If you then navigate to the Input and expand to select the column, you will see a custom property called MapFlags. This stores the operation enumeration, as shown in below screen shot.


If your component uses custom column properties in this way, these are perhaps the best candidates for a custom user interface. Using the Advanced Editor to navigate columns and set properties correctly carries a much higher risk of error and is more time-consuming for the user than a well-designed user interface. Unfortunately, this does raise the complexity of the user interface somewhat, particularly from the Windows Forms programming perspective, as the effective use of form controls is what determines the success of such a UI. However, if you are still reading this Adding a User Interface to Your Component Topic, you are probably comfortable with such challenges.
To persist these column-level properties, simply call the appropriate SetColumnTypeProperty method on the design-time interface, IDTSDesigntimeComponent100. Obviously, you want to ensure that you previously created the actual properties. For example, in the following code, a property is being set on an input column:

IDTSInput100 _input = ComponentMetadata.InputCollection[0];
IDTSInputColumn100 inputColumn =
“PropertyName”, propertyValue);

Other UI Considerations

Any good user interface should be designed with usability, accessibility, localization, and other such principles in mind. That means that the user interface should not require a mouse to be configured — the user should be able to navigate using only the keyboard just as easily. Descriptions should be clear, and strings and controls should be tested to ensure that any resizing operation does not truncate them. If the component is intended to be sold to customers, localization (and globalization) may be something you want to think about. You can find a lot of information on  on these topics, but as a start you should ensure that string literals live in resource files and that right-to-left language users are not confused by the interface.

Test the component and ensure that it does not crash when receiving invalid input, that any error messages are descriptive, and that exception recovery is graceful. Also keep in mind that users may intentionally or mistakenly use the default UI (Advanced Editor) for the component and corrupt the state that may otherwise have been protected by your UI. If the component is designed correctly, validation is modularized and shared by the component and its UI; however, if this is not possible, then try to ensure that the UI does not break if the metadata is corrupt.

Explore SSIS Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

Remember that both the component and its UI may need to be deployed together to other machines (depending on their intended use). If this is the case, consider building an installation script to place the files in the right folders and install them in the GAC as necessary.

List of Related Microsoft Certification Courses:

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


Popular Courses in 2018

Get Updates on Tech posts, Interview & Certification questions and training schedules