I am currently researching a project for a client to improve and centralise the interfacing of various systems.

Some of the systems will be known in certain industry’s: Microsoft Dynamics CRM, SunSystems, adDEPOT and EBMS / Ungerboeck for example, and others are bespoke systems developed internally or solely for the use of the client.

Interfaces include:

  • Financials (Invoices, Payments, Debtor Accounts)
  • Marketing Data (Prospects, Order History)
  • Orders
  • Other

Currently the systems interface in a variety of ways including but not limited to:

  • SSIS Packages
  • Bespoke .NET Applications
  • SQL Jobs
  • Custom ASP Scripts

I will go into a little more detail about the aims of the project at a later date, but the single biggest reason would most likely be sharing reusable code (for example where multiple systems are all interfacing invoices to Sun). My initial searches led me toward BizTalk and Jitterbit:

Microsoft BizTalk

  • Likely that the Enterprise edition would be required which is pretty expensive
  • Seems to provide a good solution to some common obstacles such as scalability and

JitterBit

  • Open Source option- although it appears to be limited so the commercial edition would probably be required
  • Essentially appears a great tool for simple interfaces but not really cut out for the complexity of most of the integrations I’m looking at

A few links that may be worth checking out if you’re considering the above:

http://blogs.msdn.com/b/chrisromp/archive/2008/02/15/using-biztalk-vs-rolling-your-own.aspx
http://stackoverflow.com/questions/61437/what-are-some-viable-alternatives-to-biztalk-server
http://stackoverflow.com/questions/378088/jitterbit-vs-biztalk

The research and discussion with the IT team (along with a very brief trial of both applications) has led us to the conclusion that neither suit our requirements that well and a "home-grown" solution is the way to go. I tried to sketch what the end product may look like:

The red lines represent links which may not exist: Should all communication go through our custom web service? Or should we connect directly to the central database for certain functions/queries? Or possibly even directly to the underlying system? Hopefully I will be able to firm this up at a later date.

The first step will most likely be to focus on a single interface: Building the necessary web service methods, invoking application and supporting logging tables in an underlying database. Sun is most probably the biggest player in all these interfaces so we’ll start with one of the interfaces to/from Sun.

SunSystems Connect (SSC): A web service already exists to communicate with Sun however we still want to wrap this within our own web service. I started by browsing to SSC and executing a query using the web interface:

But I kept hitting errors:

The following error(s) occurrent while executing the method:

An error occurred while exporting data
Unknown column specified in filter definition. Unknown column name was 'Column id = ERROR: invalid element name '/Accounts/AccountCode ''.
Column requested does not exist in the column dictionary. Column id is 'Column id = ERROR: invalid element name '/Accounts/AccountCode ''

I guess there must be a bug as the payload.xml appears malformed:

When I fix it:

And execute using my fixed payload.xml:

Everything works as intended:

Now it’s time to try communicating with SSC from .NET. Finding documentation was a bit of a nightmare but I managed to get my hands on some old paperwork: SunSystems Connect 521 SP1.pdf which includes code snippets including C#, Visual Basic.NET and Java- so far, so good…

Watch this space!

L

Advertisements