Building Custom SSIS Azure Service Bus Components


Over the last couple of weeks, I have been experimenting and working on a set of custom SSIS Service Bus components that will ultimately be the basis of the ETL framework described in my previous post (HERE). While these components are being developed as a proof of concept to test out my theories on they should be generally useful in other use cases as well. As such I am making these components (and their associated sources) available to community through a new Codeplex project (https://ssisservicebus.codeplex.com/) for you to use and abuse.

Overview

The SSIS components set forth are all built to allow for straight-forward interaction with Azure Service Bus (What is it? Go HERE) Topics and Queues. There are five components included in the initial project (more to come later) including a custom connection manager, for each enumerator, a log provider and two control flow tasks that allow you to send, receive and acknowledge messages in the form of a handshake.

image

I’ll apologize in advance for the cheesy and irrelevant icons but I was less worried about how the components looks on the design surface and more interested in how they work at runtime. So without further ado, a summary of each component.....

Azure Service Bus Connection Manager

The connection manager supports connections to Azure Service Bus Queues, Topics and Subscriptions. The AcquireConnection method returns the base class MessageClientEntity for the configured type. To simplify my life, I made the decision to require each task to ensure that the connection is appropriate rather than implement different connection managers. To facilitate the design time experience the “Test Connection” button uses the NamespaceManager to validate the specified configuration.

image

Brokered Message ForEach Enumerator

This custom foreach enumerator allows brokered messages to be read off the queue or subscription using the SSIS For Each container. The enumerator supports individual and batch based reads and can be configured with a message cap or maximum number of messages to read before exiting. Brokered message objects can be piped into an object variable using the variable mappings to allow for messages to be consumed by other tasks.

image

Brokered Message Handshake Task

The handshake task when mapped to a variable containing a Brokered Message object, can be configured to call the complete or abandon method thereby either finishing the conversation or abandoning the message so it can be processed again at a later time. This handshake task is an essential part to processing brokered messages within the control flow and can be handled either synchronously or asynchronously.

image

Send Brokered Message Task

The last task is capable of sending a brokered message to a queue or topic depending on the configuration of the connection manager. The brokered message data is either sourced from a SSIS variable or is a directly input string and can be sent synchronously or asynchronously.

image

Service Bus Log Provider

A custom log provider is included to allow SSIS log events and messages to be piped to a configured topic or queue. The log provider requires a Service Bus Connection Manager and loads the Brokered Message properties collection with the SSIS log data. All log messages are sent asynchronously.

image

The current implementation of these components is relatively sparse in terms of Azure Service Bus whiz-bang features as they simply send and receive. The plan as of now is to build them out iteratively, adding features as needed. Also note, that while I have made an effort to ensure that the design time UI experience is inline with the out of the box SSIS controls, I have not added robust design-time error handling and reporting.

At this point while production ready is a ways off, I would invite you to check the project out. If you are interested in contributing either your time, ideas or feedback I would gladly welcome it. If you have any questions, feel free to contact me directly.

 

Till next time!

Chris

Skip to main content