Part 2 - Adding a Custom UI to the Connection Manager

Ken Ross included in business-intelligence development

2013-08-07 1341 words 7 minutes

Contents

In my previous post I explained how to create a custom connection manager for SSIS to connect to RabbitMQ.

In this post I’m going to extend this further by adding a custom UI where the connection can be configured and tested.

Preparing the Project

The first step is to add a few more references:

Microsoft.SqlServer.Dts.Design
Microsoft.SqlServer.DTSPipelineWrap
Microsoft.SqlServer.DTSRuntimeWrap
Microsoft.SqlServer.PipelineHost

These assemblies can be found in C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies

The next step is to add two new files, the first is an empty class file which I’ve called RabbitMQConnectionManagerUI.cs the second is a Windows Forms class which I’ve called RabbitMQConnectionManagerUIForm.cs

For SSDT BI to know that there is a custom user interface associated with this connection manager the DtsConnection attribute on the RabbitMQConnectionManager class will need to be updated, by setting the UITypeName property.

The UITypeName refers to the fully qualified type name of the class added above, in the next section I’ll be showing the interface that this class will need to implement. The fully qualified type name looks like this:

SSISRabbitMQ.RabbitMQConnectionManager.RabbitMQConnectionManagerUI, SSISRabbitMQ.RabbitMQConnectionManager, Version=1.0.0.0, Culture=neutral, PublicKeyToken=abc123…

The PublicKeyToken can be tricky to get if you’ve never had to find it before. To get the PublicKeyToken of your assembly, open the Visual Studio Tools Command Prompt.

Browse to where the assembly is and type the following:

sn.exe -T SSISRabbitMQ.RabbitMQConnectionManager.dll

This will output the Public key token, like this:

The DtsConnection attribute now looks like this:

1
2
3
4


[DtsConnection(ConnectionType = "RABBITMQ",
DisplayName = "RabbitMQ Connection Manager",
Description = "Connection manager for RabbitMQ",
UITypeName = "SSISRabbitMQ.RabbitMQConnectionManager.RabbitMQConnectionManagerUI, SSISRabbitMQ.RabbitMQConnectionManager, Version=1.0.0.0, Culture=neutral, PublicKeyToken=ac1c316408dd3955")]

Creating the Custom UI

So at this point the project has been setup with all the required classes and the DtsConnection attribute has been updated so SSDT BI will know that there is a custom UI to configure the connection. Now the fun begins.

The first step is to open the RabbitMQConnectionManagerUI class file and have it implement the IDtsConnectionManagerUI interface.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22


public class RabbitMQConnectionManagerUI : IDtsConnectionManagerUI
{
  public void Delete(IWin32Window parentWindow)
  {
    throw new NotImplementedException();
  }
  
  public bool Edit(IWin32Window parentWindow, Connections connections, ConnectionManagerUIArgs connectionUIArg)
  {
    throw new NotImplementedException();
  }
  
  public void Initialize(ConnectionManager connectionManager, IServiceProvider serviceProvider)
  {
    throw new NotImplementedException();
  }
  
  public bool New(IWin32Window parentWindow, Connections connections, ConnectionManagerUIArgs connectionUIArg)
  {
    throw new NotImplementedException();
  }
}

After Visual Studio has created all the stubs, we can see there are four methods which need to be implemented, I will come back to these after creating the Windows Form class.

Next I opened the RabbitMQConnectionManagerUIForm and added a bunch of UI components so it ended up looking like this:

(All the code for the Form will be available in my GitHub repository so I’m only going to cover off the important/interesting parts.)

The next step was to add a new constructor to the Windows Forms class so that some important parameters can be passed through from the RabbitMQConnectionManagerUI, this will be the class which instantiates the Form.

1
2
3
4
5
6
7
8


public RabbitMQConnectionManagerUIForm(Microsoft.SqlServer.Dts.Runtime.ConnectionManager connectionManager, IServiceProvider serviceProvider)
: this()
{
  this.connectionManager = connectionManager;
  this.serviceProvider = serviceProvider;
  
  SetFormValuesFromConnectionManager();
}

The connection manager that is being passed in is what the Form is actually going to be modifying.

The SetFormValuesFromConnectionManager method updates the Form each time it is loaded with the currently set values for the Connection Manager.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29


private void SetFormValuesFromConnectionManager()
{
string hostname = connectionManager.Properties["HostName"].GetValue(connectionManager).ToString();
string username = connectionManager.Properties["UserName"].GetValue(connectionManager).ToString();
string password = connectionManager.Properties["Password"].GetValue(connectionManager).ToString();
string virtualhost = connectionManager.Properties["VirtualHost"].GetValue(connectionManager).ToString();
string port = connectionManager.Properties["Port"].GetValue(connectionManager).ToString();
 
if (!string.IsNullOrWhiteSpace(hostname))
{
txtHost.Text = hostname;
}
if (!string.IsNullOrWhiteSpace(username))
{
txtUserName.Text = username;
}
if (!string.IsNullOrWhiteSpace(password))
{
txtPassword.Text = password;
}
if (!string.IsNullOrWhiteSpace(virtualhost))
{
txtVirtualHost.Text = virtualhost;
}
if (!string.IsNullOrWhiteSpace(port))
{
nmPort.Text = port;
}
}

One of the important parts of the RabbitMQConnectionManagerUIForm class is what happens when the user clicks the OK button. In the event handler below I’m setting the DialogResult to be DialogResult.OK, this is so that the calling class knows the user has accepted the changes. As well as this there is also a call to the UpdateConnectionFromControls method, this method will go through each property in the connection manager and update them to what has been set in the Form.

1
2
3
4
5
6
7


private void btnOK_Click(object sender, EventArgs e)
{
  UpdateConnectionFromControls();
  
  this.DialogResult = DialogResult.OK;
  this.Close();
}

The UpdateConnectionFromControls method looks like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


private void UpdateConnectionFromControls()
{
  int port = Convert.ToInt32(nmPort.Value);
  
  connectionManager.Properties["HostName"].SetValue(connectionManager, txtHost.Text);
  connectionManager.Properties["UserName"].SetValue(connectionManager, txtUserName.Text);
  connectionManager.Properties["Password"].SetValue(connectionManager, txtPassword.Text);
  connectionManager.Properties["VirtualHost"].SetValue(connectionManager, txtVirtualHost.Text);
  connectionManager.Properties["Port"].SetValue(connectionManager, port);
}

The code for the Test Connection button attempts to create a connection to the RabbitMQ broker using the values specified in the form.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34


private void btnTestConnection_Click(object sender, EventArgs e) 
{
  IConnection connection = null;

  try 
  {
    ConnectionFactory rabbitMqConnectionFactory = new ConnectionFactory() 
    {
      HostName = txtHost.Text, VirtualHost = txtVirtualHost.Text, UserName = txtUserName.Text, Password = txtPassword.Text, Port = Convert.ToInt32(nmPort.Value)
    };

    connection = rabbitMqConnectionFactory.CreateConnection();

    if (connection != null && connection.IsOpen) 
    {
      MessageBox.Show("Test connection verified", "RabbitMQ Connection Manager", MessageBoxButtons.OK, MessageBoxIcon.Information);
    } else {
      MessageBox.Show("Test connection failed", "RabbitMQ Connection Manager", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }

    connection.Close();
  } 
  catch (Exception ex) 
  {
    MessageBox.Show("Test connection failed!" + Environment.NewLine + ex.Message, "RabbitMQ Connection Manager", MessageBoxButtons.OK, MessageBoxIcon.Error);

    if (connection != null && connection.IsOpen) 
    {
      connection.Close();
    } else if (connection != null) {
      connection.Dispose();
    }
  }
}

This is all I’m going to cover in terms of the Form, for the full class check it out on GitHub.

Now it is time to go back to the RabbitMQConnectionManagerUI. At the moment this class has four methods that need to be filled in:

Initialize
New
Edit
Delete

The Initialize method is very straightforward, I am simply just setting a reference in my class to the Connection Manager and Service Provider that was passed through to it. This is so they can then be passed onto the Form class when it is being instantiated, as you will see below.

1
2
3
4


public void Initialize(ConnectionManager connectionManager, IServiceProvider serviceProvider) {
  this.connectionManager = connectionManager;
  this.serviceProvider = serviceProvider;
}

The New method is called by SSDT BI after the Initialize method, when the user is creating a new Connection Manager. It is suggested by the MSDN documentation that in here the Form is displayed for editing.

1
2
3
4
5
6
7
8


public bool New(IWin32Window parentWindow, Connections connections, ConnectionManagerUIArgs connectionUIArg) {
  IDtsClipboardService clipboardService;

  clipboardService = (IDtsClipboardService) serviceProvider.GetService(typeof (IDtsClipboardService));
  if (clipboardService != null) // If connection manager has been copied and pasted, take no action. { if (clipboardService.IsPasteActive) { return true; } }

    return EditConnection(parentWindow);
}

The EditConnection method is used by both the New and Edit methods defined on the interface and is used to launch the Form:

1
2
3
4
5


private bool EditConnection(IWin32Window parentWindow) { RabbitMQConnectionManagerUIForm frm = new RabbitMQConnectionManagerUIForm(connectionManager, serviceProvider);

var result = frm.ShowDialog();

if (result == DialogResult.OK) { return true; } else { return false; } }

In the Edit method I am simply just displaying the Form using the handy helper method from above, so the user can edit the connection.

1
2
3
4


public bool Edit(IWin32Window parentWindow, Connections connections, ConnectionManagerUIArgs connectionUIArg) 
{ 
  return EditConnection(parentWindow); 
}

The Delete method is for any cleanup that is required when the Connection Manager is delete from the package, as we don’t need to perform any cleanup the method is very simple:

1

public void Delete(IWin32Window parentWindow) { }

What it looks like

At this point I can now build the project and open SSDT BI to see the result.

Stay Tuned! In the next post I will show how to create a Custom Source which uses the connection manager to read messages from a RabbitMQ queue!

🍪 I use Disqus for comments

Because Disqus requires cookies this site doesn't automatically load comments.

I don't mind about cookies - Show me the comments from now on (and set a cookie to remember my preference)