Marcus Rosen.NET

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 3^rd 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 3^rd 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.