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.