SQL Server Integration Services and BizTalk Server are two widely used Microsoft tools to build integration, Data Synchronization, and business process workflows solutions. The purpose of this article is to help the reader select the right tool for a project.

SSIS is a great ETL tool and is very useful in dealing directly with data for the purpose of migration, merging or synchronizing between different data repositories. SSIS packages are simpler to develop, deploy, and maintain. Whereas the main strength of BizTalk is workflow and process management and Integration between external/internal systems. BizTalk is a more powerful tool, but it takes more development time and is somewhat complex to deploy and troubleshoot.

Typical Uses of SSIS:

  • Merging Data from Heterogeneous Data Stores
  • Populating Data Warehouses and Data Marts
  • Cleaning and Standardizing Data
  • Building Business Intelligence into a Data Transformation Process
  • Automating Administrative Functions and Data Loading

Typical uses of BizTalk:

  • Enterprise Application Integration
  • B2B Process Implementation
  • Process Workflow Implementation

Overlapping Capabilities

Implementing a Typical SSIS scenario using BizTalk is not very feasible, because is costlier to develop and maintain than SSIS. When it comes to workflows and process management scenarios, you may want to consider implementing these using SSIS rather than BizTalk Server. This is because at times it may take considerably less development time and also result in low maintenance for the simple scenarios.

Selection of the right tool is very important for the success of your project, since sometimes using a simple tool to build a complex business process can end up in lots of modifications and high maintenance costs. When choosing between BizTalk Server and SSIS answer the following questions:

  • Do you need a quick solution? If you want to develop anything rapidly consider using SSIS as it takes far less time than BizTalk to build simple or moderately complex integration scenarios. (Reasons for a Quick Solution may be anything like the client has a small budget, or the application is going to be used for only six to twelve months and won’t require much maintenance.)
  • Does your team have good experience with BizTalk? Well, if not then probably learning BizTalk is going to be a tedious task for your team so consider using SSIS.
  • Do you need to implement Point-to-Point integration involving 2-3 applications? This can be achieved using SSIS quiet easily.
  • Dealing with enterprise standards like RosettaNet, HL7, HIPAA and SWIFT? Consider using BizTalk as it comes with Accelerators that offer support for these standards.
  • Do you expect long running transactions? Long Running Transactions are processes that must wait for some event for an indefinite time (from a few hours to days) to complete the process flow. BizTalk has built-in support for long running transactions and it handles it quite efficiently.
  • Do you intend to implement ESB pattern?  BizTalk Server should be your clear choice as it comes with ESB Toolkit, which is a collection of tools and libraries that extend BizTalk Server capabilities of supporting a loosely coupled and dynamic messaging architecture.
  • Do you need to implement an EDI based process? BizTalk Server provides built-in Electronic Data Interchange (EDI) functionality supporting X12 and EDIFACT
  • Implementing a mission critical workflow that needs recovery process? BizTalk is a great tool that proves persistence and recovery of failed messages without blocking subsequent messages.
  • Does your solution need more integration points in the future? If it’s a BizTalk application, it may be just a matter of configuring another publisher or subscriber to your process whereas in SSIS you may need the latest another data flow
  • Implementing message/asynchronous integration? BizTalk provides message routing capabilities such as forking and aggregating message flows and content-based routing.
  • Interaction with industry standard LOB Systems required? BizTalk provides rich adapters to simplify integration to Line Of Business (LOB) applications like Siebel, SAP, IFS Applications, JD Edwards and Microsoft Dynamics CRM
  • Looking for close to real-time integration? BizTalk is more efficient for processing individual messages. A separate instance is created for each message.

Conclusion

Use SSIS if:

  1. The application is very simple and needs basic workflow capabilities and will not need advanced features like EDI, long running transactions and implementing ESB.
  2. You are implementing highly data intensive operations with small workflow processes.
  3. You are creating an ad hock process that will not to be maintained for a long time. An example of an ad hock process may be an interim interface between the Legacy and the future system to keep them synchronized until all dependent applications are pointing to a new system.

Consider using BizTalk if one more of following are true:

  1. You are implementing very complex processes that will need advanced features like EDI, long running Transactions and implementing ESB pattern.
  2. You are creating an integration process that will remain in service for a long period of time and will integrate more applications/services during the course of its life.