We are looking to centralise our Integration in our company and I'm wondering what sort of solutions other have for this and what the best way to approach it would be. At the moment we have different types on integration running different lanuages, different frameworks on different servers so we are looking to move them all to a single framework to make it easier to manage.

Some of the current Integration includes:

  • SQL Server scheduled tasks (on 3 or 4 servers)
  • SQL Server Integration services (on 2 or 3 servers)
  • Console applications (.NET on 2 Servers)
  • Windows Services (.NET on 2 servers)

What we are after is:

  • 1 server that manages all integration jobs scheduling (so this only needs to be setup once and we always know where to find it)
  • A job monitor that displays the history of jobs run (Much like the SQL Server Job Activity monitor)
  • Jobs might need to run custom .NET code, here some sort of API might be used to allow for more flexibility over SQL Server Integration Services
  • I'm kinda in your boat, but we aren't looking yet, we have scheduled tasks, SQL Jobs, Windows Services, etc doing all kinds of data moving. Interested to see the answers. – BlackICE Jun 2 '11 at 10:47
  • I've never heard of the term "centralized integration" before. I can take what I would assume is a pretty good guess based on your question, but can you elaborate or perhaps use other terms to solidify exactly what you mean? – Thomas Owens Jun 10 '11 at 0:09

Integration points are a perfect place for an ESB. They can just be activates events based upon messaging.

This can allow you have to a central service that either uses the framework and itself activates other actions via a message. These messages can be distributed locally or remotely, to services that are waiting for the messages and will activate upon receiving them to complete whatever action you want.

  • Scheduler says it's time to run stored proc to move data from db to another (an example of possible scheduled job)
  • Scheduler sends a TransferData message
  • A service is awaiting this message, attached to the service bus, that activates a stored proc when the message is received
  • The service can publish a ActivityStatus message to the service bus
  • A logging activity can listen for the ActivityStatus message and record the status of the event for reporting purposes

Now an ESB buys you a few things here. One you can easily remove the service that awaits the TransferData message and replace it with another one that handles that message in a different manner.

Additionally if you have data imports with large imports files, you can break them up. It's not hard to have a service that listens for file drops then reads in the file and generates either 1 message saying there's data ready or breaks up the file into each record in the large file to be processed. Breaking up the file allows you to more easily track errors with a specific record, fix them, and repost them to the system but sometimes ordering matters more. With having each record in the large file as a single message, you can also scale out multiple services that consume the same message, makes the file processing happen in parallel.

In .NET you really have two frameworks that fit as an ESB. MassTransit or NServiceBus. If you really want, you try BizTalk from MS but that will likely make you pull your hair out. Though you can use the Workflow Designer if you have non developer users that will be setting up the jobs.

I know MassTransit supports scheduled events in some fashion, I assume that NServiceBus does as well though I don't know that for sure.

  • I hope my edits make it more clear and start pointing you in the right direction. – Travis Jun 10 '11 at 0:51

Have you considered TeamCity? It has got many plug-ins which might prove helpful. There is another option - Cruise Control .NET, but it will require more configuration. Lastly, you may want to check TFS server, keep in mind, however, the cost of this CI.

  • 1
    Sorry, but I'm not looking for a continuous integration solution but something to centralise all the companies integration between its many components. – d1k_is May 3 '11 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.