SQL Server 2005 Integration Service (SSIS) vs. BizTalk Server 2006 (Updated)

Wednesday, April 02, 2008

EDIT: Post has been update after comments by Rick Negrin (Microsoft).

The other day I was asked about the differences about SSIS and BizTalk as there are some blurred boundaries between these products. Not being an expert on SSIS, this write up might be a bit bias towards BizTalk but hopefully will provide some useful comparisons or insights.

What is SQL Integration Services (SSIS)?
SSIS is a replacement for Data Transformation Service (DTS) and provides tools to build data integration solutions, including extraction, transformation, and load (ETL) for data warehousing. SSIS can be used to build solutions to transfer/copy data between multiple different data sources (Database table, Flat File, Excel, etc) using a variety of protocols (SQL, FTP, SOAP, etc) allowing for transformation/cleaning/aggregation between those data sources. SSIS also provides a .NET Application Programming Interface (API) and can be invoked from .NET code.

What is BizTalk Server?
BizTalk is an Enterprise Integration (EAI) and Business Process Management (BPM) Server. It provides tools to create both long-running and transactional business processes allowing for interaction with multiple platforms through adapters supporting multiple protocols (SQL, File, SOAP, HTTP, POP3, etc) and applications (SAP, Siebel, JDE, etc). BizTalk includes a Rule Engine allowing for real-time execution of business rules, a XML transformation engine, real-time monitoring framework for better visibility of business processes and more.

Overlapping features of BizTalk and SSIS

  • Integration
    SSIS provides integration on the database/physical file level and some support for integration with a business process e.g. through SOAP (although this is not typical usage of the product). It’s typically used to execute batch processes without specific or changing business rules. A typical integration scenario for SSIS is Database to Database integration or File to Database (or vice versa) batch process that is scheduled to run at a given time.

    BizTalk provides integration on the business process level but also support for database level integration. BizTalk can receive data from both databases and files, execute a set of discrete business step and write data back to a file of database. BizTalk’s strength lies in real-time data, orchestration of business processes and ability to connect many different systems and applications. BizTalk adapters also have features such as automatic retries and service windows. A typical integration scenario for BizTalk is a multi-step business process requiring specific business rules to be executed and multiple systems to be interacted with e.g. an order and credit-card approval process through a 3rd party web service.
  • Transformation
    SSIS support transformation between many formats, e.g. comma separated files, Excel files, and database table but only offers very limited support for XML. XML is supported only as a data type (e.g. to be inserted into a table) but XML cannot be transformed to Flat File format without addition of custom code.

    BizTalk supports mainly transformations of XML to XML. The reason for this is that all data that goes through the BizTalk engine need to be converted to XML. BizTalk has support for flat files as an XML schema but the mapping between transformation formats is done on the XML level. Custom pipeline components can be created to support transformation to/from other formats.
  • Messaging
    Service Broker (part of SSIS) is First-In-First-Out (FIFO) highly-scalable queue used for asynchronous and reliable processing. As it’s a FIFO no parallel processing is available which decreases throughput. The Service Broker doesn’t support subscriptions and needs to be explicitly invoked (e.g. via SOAP, Stored Procedure or through SSIS directly). There is no support for automatic retries or service windows. The typical scenario for Service Broker is to handle low priority asynchronous call from an SSIS process such as sending emails without holding up the existing process.

    BizTalk features a powerful publish and subscribe messaging system. New message subscribers can be added with ease (after deployment) and content-based routing is easy to implement. It supports both parallel and serial (FIFO) processing. All adapters also support automatic retries and service windows. All messages are stored transactionally in the MessageBox database.

Scenarios

  • File Order Batch
    A company’s proprietary order system provides a flat file of the days orders exported from the Mainframe. The order batch data needs to be inserted into a ODBC database table.
     
    • SSIS – Everything can be done out of the box.
    • BizTalk – Everything can be done out of the box but SSIS is would most likely be more suitable (unless the file needed to be processed at the time it was created rather than at a scheduled time).

  • Simple Order Exporter
    At a given interval, retrieve XML order data from a database, transform data, and send transformed data to a web service (which provides a thin wrapper of a database). Receive web service reply and handle error conditions based on reply or errors.

    • SSIS – Everything except the XML transformation and the handling of reply can be done without custom code. If the format could be changed and the service boundary removed (talking directly to underlying database) SSIS would be a good choice.
    • BizTalk – Everything can be done out of the box.

  • Order Batch and Credit Card Validation
    At a given interval, receive order batch (as XML), perform rules on each discrete order (and react to rule outcome), call 3rd Party web service for order and credit card verification and run rules on the returned result (and react to rule outcome).

    • SSIS – Everything except running business rules can be done without custom code. Every time a business rule changes (if we assume it’s been implemented in .NET code) the .code has to be updated, recompiled and deployed. Processing can only be done in serial, as one batch.
    • BizTalk – Everything can be done out of the box with little custom-code both in parallel (by splitting the batch and processing each order individually) and in serial, as one batch.

Summary

SSIS strength lies in bulk data processing on the database/file level. SSIS is a great tool for implementing ETL solutions but not a good tool for implementing business processes on. If data needs to be interpreted, business rules needs to be evaluated and multiple steps are involved BizTalk is most likely a better choice.

BizTalk strength lies in supporting complex processes on the business level still with good support for data level integration. To use BizTalk for database to database integration without/or with few business rules is an over-kill solution and SSIS would most likely be a better fit.

Posted by Marcus Rosen at 1:29 AM  

10 comments:

The description of Service Broker above is completely inaccurate.
Service Broker. Service Broker is a FIFO queue but supports the concept of conversations (think TCP sessions). A conversation is a logical grouping of messages and gurantees exactly once in order. You can have many converstaions active on queue at one time and the conversations can be processed in parallel. Service Broker is an async, reliable network protocol so once you send the message the Service Broker infrastructure will try to send the message, if it can't make a connection it will keep trying until it is successful or the conversation is ended. Service Broker is highly scalable. We have customers moving on the order of 20,000 messages per second. It is used by customers in a variety of ways including real time data integration. It is true that Service Broker does not yet support a publish subscribe model. This is something we are looking at building.

Rick Negrin
SQL Server Program Manager

Anonymous said...
2:49 AM  

Rick,

Thanks for you feedback. The post has been updated.

4:00 AM  

Thanks for this post.

I'm currently studying up on BizTalk for a role involving finding out where and when it would help partner and customer businesses. One thing I need to learn is when they should use BizTalk, and when I should get them to talk to the SSIS team. This post was a good summary for me to start from.

Anonymous said...
1:29 AM  

useful points. However, one clarification about the Biztalk messages. They do not have to be XML. You can pass any binary format message as well through the biztalk engine when you use pass through pipelines as you dont need to know anything about the message for those pipelines, but if you need to do any promotion or other disassembly (in a pipeline), then you need to recognize the message and there you think about xml etc.
Cheers,
Benjy

Anonymous said...
5:12 AM  

In the first scenario "File Order Batch", you say that SSIS is more suitable for processing files on a scheduled basis but not when the files are created. My shallow understanding so far of SSIS is that in can monitor and handle OS-level events which I interpret as being able to process a file at the time it's created, yes?

Unknown said...
4:52 AM  

Emad, Is this an out of the box feature of SSIS? Or does it involve custom code like hooking into WMI or using a FileWatcher?

5:31 AM  
This comment has been removed by the author.
5:31 AM  

Marcus,

As far as I know there will be some custom code involved to handle OS-level events, however there is a huge elephant here that nobody is addressing, and that is cost. There is a huge difference in cost between SSIS and Biztalk, so from that perspective writing some custom components might be well worth it.

Unknown said...
8:23 AM  

Clearly BizTalk is a generally a more expensive solution than SSIS, there is no arguments there.

What the original post was trying to high-light was where the abilities of each platform overlap. As stated my background is in BizTalk which is why I welcome comments like yours.

I guess the bottom line is if you want to do data or file level integration use SSIS and if you need to do more business process level integration use BizTalk (if feasible).

9:28 AM  

I am impressed by the quality of information on this website. There are a lot of good resources here. Thanks for sharing !

5:54 PM  

Post a Comment