Part 1 - Building a Custom Connection Manager

Following on from my introductory post, here I will be explaining how to develop a Custom Connection Manager for RabbitMQ. This connection manager will then be used in future posts by a custom Source and Destination component.

What is a Connection Manager for?

From the MSDN documentation:

“Integration Services uses connection managers to encapsulate the information needed to connect to an external data source. […] If the connection managers and external data sources supported by Integration Services do not entirely meet your requirements, you can create a custom connection manager.”

By the sounds of it, creating a Connection Manager should be pretty straightforward:

1. To create a custom connection manager, you have to create a class that inherits from the ConnectionManagerBase base class,
2. Apply the DtsConnectionAttributeattribute to your new class,
3. Override the important methods and properties of the base class, including the ConnectionString property and the AcquireConnection method.

Prerequisites

All of these examples will be designed against the SDK for SQL Server 2012. You will need to ensure you installed the Client SDK during the installation of SQL Server.

Setting up the solution

I created a new solution and added a new class library projects to it called SSISRabbitMQ.RabbitMQConnectionManager.

To this project I then added the RabbitMQ client library via NuGet:

The next step is to add a reference to the Microsoft.SqlServer.ManagedDTS.dll, from the documentation:

“The Microsoft.SqlServer.Dts.Runtime namespace contains the classes and interfaces to create packages, custom tasks, and other package control flow elements.”

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

There are a few other assemblies in this directory that will be used in later stages.

For SSDT BI to pick up the custom connection manager it needs to be stored in the GAC, as well as a few other folders in the SQL Server directory.

The next step is to create a post build action that will automate this each time the project is built. Open the project properties by double clicking on Properties or by going to the Project menu and selecting “SSISRabbitMQ.RabbitMQConnectionManager Properties…”

Click Build Events and the paste the following into the Post Build event text area:

1
2
3
4
5
6

"C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\gacutil.exe" -u $(TargetName)
"C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\gacutil.exe" -iF $(TargetFileName)
copy $(TargetFileName) "C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Connections\$(TargetFileName)" /y
 
copy "$(TargetDir)RabbitMQ.Client.dll" "C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PublicAssemblies" /y
copy "$(TargetDir)RabbitMQ.Client.dll" "C:\Program Files\Microsoft SQL Server\110\DTS\Binn" /y

The last step to setting up the project is to sign the assembly, the reason for this is because the assembly that will be built is going into the GAC. All assemblies that go into the GAC need to be signed with a strong name. See here for more details.

With the properties still open click on “Signing” .

Tick the checkbox “Sign the assembly”, select <New…"> from the “Choose  a strong name key file” drop down, and I created one called ssisrabbitmq.snk

The solution so far looks like this:

Creating the Connection Manager

Now that the solution is setup with all dependencies it is time to start on the actual connection manager class.

Start by adding a new class file “RabbitMQConnectionManager.cs”, to this class apply the DtsConnection attribute. There are three properties which will need to be set:

• ConnectionType
• DisplayName
• Description

These are the properties which will appear in SQL Server Data Tools BI when a user goes to add a new connection.

The next step is to extend the ConnectionManagerBase class.

This is the code so far:

1
2
3
4
5

[DtsConnection(ConnectionType = "RABBITMQ",DisplayName = "RabbitMQ",
               Description = "Connection Manager for RabbitMQ")]
public class RabbitMQConnectionManager : ConnectionManagerBase
{
}

The next step is to add a number of properties which will be used for the actual connection to the RabbitMQ broker:

1
2
3
4
5

public string HostName { get; set; }
public string VirtualHost { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
public int Port { get; set; }

For the sake of speedy development, in the constructor I’m setting the default properties for the connection.

1
2
3
4
5
6
7
8

public RabbitMQConnectionManager()
{
  HostName = "localhost";
  VirtualHost = "/";
  UserName = "guest";
  Password = "guest";
  Port = 5672;
}

Validating the Connection Manager

By overriding the Validate method, SSDT BI is able to make sure that the user has configured the connection correctly. If the method returns DTSExecResult.Failure then the package won’t begin execution.

 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

public override Microsoft.SqlServer.Dts.Runtime.DTSExecResult Validate(Microsoft.SqlServer.Dts.Runtime.IDTSInfoEvents infoEvents)
{
  if (string.IsNullOrWhitespace(HostName))
  {
    return DTSExecResult.Failure;
  }
  else if (string.IsNullOrWhitespace(VirtualHost))
  {
    return DTSExecResult.Failure;
  }
  else if (string.IsNullOrWhitespace(UserName))
  {
    return DTSExecResult.Failure;
  }
  else if (string.IsNullOrWhitespace(Password))
  {
    return DTSExecResult.Failure;
  }
  else if (Port <= 0)
  {
    return DTSExecResult.Failure;
  }
 
  return DTSExecResult.Success;
}

Setting up the connection to RabbitMQ

The next two methods to override are AcquireConnection and ReleaseConnection.

These methods are called at various times during Design Time and Run Time.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

public override object AcquireConnection(object txn)
{
  ConnectionFactory connFactory = new ConnectionFactory()
  {
    UserName = UserName,
    HostName = HostName,
    Password = Password,
    Port = Port,
    VirtualHost = VirtualHost
  };
 
  var connection = connFactory.CreateConnection();
 
  return connection;
}

In the AcquireConnection method we are basically just setting up the connection to RabbitMQ. (As I am aiming to be as simple as possible I’m leaving out a lot of exception handling, so beware!)

If you aren’t familiar with the RabbitMQ API for C# then it is definitely worthwhile to have a read through the user guide which can be found on the RabbitMQ website here.

The next method is ReleaseConnection:

1
2
3
4
5
6
7

public override void ReleaseConnection(object connection)
{
  if (connection != null)
  {
    ((IConnection)connection).Close();
  }
}

Here we are closing the connection.

The solution so far

A custom connection manager which users can use to add connections to RabbitMQ into their packages. This is obviously useless until the Source and Destination components have been added.

After building the solution and creating a new Integration Services project in SSDT BI I can now add the connection manager into a new package:

The properties are also visible:

That is it for now on the custom connection manager.

Stay tuned, in the next post I will be showing how to create a custom source which uses the RabbitMQConnectionManager custom user interface for the RabbitMQConnectionManager.

UPDATE:

Something that I forgot to mention, because of the post build action calling the gacutil.exe you will need to run Visual Studio as Administrator.