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 four components included in the initial project (more to come later) including a custom connection manager, for each enumerator 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.

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.

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

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s