BizTalk and SSIS

April 11, 2007 · 1 min read

I’ve been interesting in using SSIS ever since I first heard about it.  Since I specialize in doing integration work, primarily with BizTalk, I was curious about this new “Integration Services” component of SQL Server.  I was a little bit worried that it would supplant BizTalk for a lot of integration scenarios (worried since I’ve invested so much time understanding BizTalk).  Since SQL2005 was released I have had a chance to read a lot more about SSIS and also to use it in a couple of projects.  I am happy to report that it does not make BizTalk obsolete.  But it is important to understand whether to use BizTalk or SSIS for certain integration problems.  After reading a few things and discussing this question with some Microsoft folks I believe that the following guidelines should apply:

Use SSIS For:

  • ETL from various data sources (Oracle DB, comma delimited data file, etc…)

  • Batch Oriented Scenarios (e.g.  files FTP’ed daily from partner systems to a drop directory)

Use BizTalk for scenarios involving:

  • Process Integration (EAI or B2Bi)

  • Near Real-time Scenarios

  • Business Process Management (BPM)

A combination of BizTalk and SSIS may be useful in certain circumstances.  I have seen situations where an enterprise needed to process a large number of complex operations specified by a partner companies.  The partner enterprises would send large files (by FTP for example) to the enterprise on a daily basis.  These files contained a list of requests for particular services that involved many steps and several enterprise systems.  However the first step is to parse and import the large file.  SSIS is best suited to import the file.  The information about the operations could be imported into SQL Server.  For example, each operation record could be inserted into a “Pending Processing” table.  BizTalk could then use the SQL adapter to pull each operation record from the table and begin the operation.  This is very much like the classic splitter pattern.

This article is part of the GWB Archives. Original Author: Walter Michel

New on Geeks with Blogs

  • We Won The One Award I Actually Care About

    Full Scale made the Inc. 5000 for the fifth year straight, the 12th listing across my three companies. Here is why the one award you cannot buy is worth stopping for.

  • Your Customers Build the Features Now

    I let a tool I liked sit dead for a year rather than build the features I wanted. An MCP server meant I never had to, and your customers can do the same to your product.

  • Get the Size of a Directory in Linux the Easy Way

    du -sh for the quick answer, ncdu for the cleanup, df for the disk itself: every command for checking directory size in Linux, plus why du and df never agree.

  • Vim Search and Replace: The Ultimate Guide

    One :%s command replaces every match in a file before a find dialog would even open. The Vim substitute patterns worth the muscle memory: flags, ranges, capture groups, and multi-file edits.