How to get a mixed SSIS-J2EE system to communicate via messaging?

Question

I'm currently developing an ETL solution which, for various reasons, include SSIS components as well as J2EE services.

I need the various components to communicate asynchronously via messaging queues. However, the obvious constraint is that SSIS only integrates with MSMQ while it obviously makes sense to use JMS on the Java side.

I have considered the MSMQ/MQSeries Bridge (we use WebsphereMQ internally) but I feel this adds another layer of complexity to the solution.

I now wonder whether there is a simpler solution to achieve cross-platform messaging. The purpose of the messaging approach is really to implement transfer of control between components, rather than pass data. Each component, whether it's a SSIS package or a J2EE service, will read/write from the same underlying database so I wonder if I'm better off just implementing a polling mechanism on either side. Suggestions are welcome.

Christophe.

Answer 1

You could use an ESB instead of JMS and use the Web Service task in SSIS to connect to and from the ESB via SOAP.

Answer 2

If all you need in J2EE->SSIS channel is ability to start SSIS package from J2EE, I think the simplest solution is to configure SQL Server Agent Job that runs this package, and then invoke sp_startjob stored proc from Java - should be way easier, and less additional components involved.

I'm not sure what's the best way to call SSIS->J2EE.

Answer 3

depending on your needs you could write your own bridge to move messages between MSMQ and WMQ. We have done pretty easily using .NET and the IBM XMS libraries.

http://www-01.ibm.com/support/docview.wss?rs=171&uid=swg24011756&loc=en_US&cs=utf-8&lang=en