Design Advice

[Chris Platts]

Hi,

I'd like to run a scenario past the group to gather some thoughts on an appropriate MT/RMQ setup for our system.

We currently have large collections of SSIS packages all connected together via SQL Agent jobs, steps to call other packages, and polling schedule databases to check progress and trigger actions.  It's unwieldy to say the least!

These collections (we call them 'builds') have to be duplicated, modified extensively, extended and re-deployed (manually!) for each new customer we gain.  To make things more complex, there's an awful lot of data being moved around during each build and many long-running imports, exports, queries and procedures.  A single execution of one build may take 2-3 days, and we're lucky if an individual step within a build takes less than a few hours.

We need to untangle this and I want to start by turning some of the simple steps into services.  For example, many of the builds compress/decompress archive files (some small, some 6GB, compressed -- so that'll take a while too).

I'm thinking of an ArchiverService which consumes messages containing requests to perform archive actions ('zip this', 'unzip that', etc).  We'd have several instances of this service so we can run tasks in parallel across hosts.

Things are made rather more complex by the fact that the client of this service will be SQL Server 2008 SSIS packages.  I could write a MassTransit package step, but they have to target .NET 3.5 for SSIS 2008 which is too old for MT.  I thought I'd get around that by building a WebAPI with SignalR support so I could build an SSIS step which calls and listens to the MT bus via SignalR.

So what I'm imagining is:

• An ArchiverService running as an MT consumer
• The ArchiverService pushes progress and completion information back to its requesters (what's the best mechanism for this?)
• A standalone Publisher assembly that can be used by non-SSIS clients to push requests to the ArchiverService and that'll raise events to its calling application based on responses from the ArchiverService
• A WebAPI that can also use the Publisher assembly to communicate with services
• A SignalR interface to that API
• A custom SSIS build component that allows publishing of messages via the SignalR interface and will raise events within the package to drive progression through the package.

Does this seem sane?  Or if not sane, then possible?

Any and all advice welcome!

Thanks,

Chris

[Chirag Patel]

Hi,

My question may be far-fetched and may not apply to your situation. But, I tend to think every distributed system would like to consider this.

Scenario:

1. One of the SSIS builds invokes a web api.

2. web api sends message via masstransit and is stored in rabbitmq (let's say).

3. But, web api is never able to respond back to the caller.

4. What is your caller going to do now?

5. Will it be able to re-try immediately or will it re-try later?
6. Let's say message is retried, will the ArchiverService be able to handle this retried duplicate message without causing any side impact? In other words, the net effect of processing this duplicate message processing should be none (in general). Not sure, what this means for your scenario.

Depending on answers to some of these small questions, it may all seem fine.

These are merely thoughts to avoid random issues on random days.

On a second thought, the solution itself is little bit weird because of SSIS being invoker as you pointed out. That is causing the complexity to build web api with signalr.

I can't believe I'm going to ask this, but here it is. Have you considered looking into sql service broker for messaging instead of mass-transit? I don't necessarily say that it will be much better. But, seems to fit in terms of technology integration here. I am imagining you would already have everything you need in order to run that.