BizTalk vs SSIS

One of the common product comparisons is where do I use BizTalk and where do I use SQL Server Integration Services.

Similarities

The overlap between these two products is that they are both capable of integrating into an application at database level and have some level of capability to integrate into applications at other levels. This means that some integration patterns can be implemented with both technologies.

Differences

• SSIS has a limited set of connectors compared to BizTalk
• BizTalk is able to receive messages from applications over many protocols where as SSIS is only able to pull data from a source like a file or a database
• BizTalk is able to do messaging patterns where as SSIS is only able to do ETL
• BizTalk is able to implement a publish and subscribe pattern pushing data to multiple data sources. SSIS can only have 1 destination
• BizTalk has more powerful transformation and enrichment options
• BizTalk is able to handle errors and retry later where as SSIS is typically within a single transaction

Thoughts

As is often the case there is no one size fits all approach but here are some thoughts which you can use as a guide:

• If you are integrating point to point from one database to another then SSIS is likely to be your best choice because of the optimized database processing performance benefits
• If you are integrating above database layer in both applications then BizTalk is likely to be your best bet
• If your receiving messages from the middle tier of an application and then pushing them to a database then BizTalk is likely to be your best bet
• If your integrating from database to middle tier they its more of a grey area as both technologies can pull from a database and push to a web service which probably covers a lot of these scenarios
• If you have a large amount of data then integrating close to the database is likely to perform better

When you get BizTalk you will also have SQL Server which will cover you for licensing to be able to develop and execute SSIS packages. This means that you have options to be able to develop solutions which combine both BizTalk and SSIS working together.

Further reading

There are some additional resources available which can help you explore this further:

• BizTalk Bill Chestnut’s User Group Presentation – Click Here