<< Starting to Blog | Home | I Like Internet Radio >>

BizTalk and SSIS

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.

Print | posted @ Wednesday, April 11, 2007 7:46 PM

Comments on this entry:

Gravatar # re: BizTalk and SSIS
by c at 5/15/2007 9:24 PM
at an interview today, i made a comment about SSIS and BizTalk after reading your blog. it really impressed the interviewer. Thank You!
Gravatar # re: BizTalk and SSIS
by Yuri Khenokh at 8/21/2008 4:34 PM
One more important difference:

BizTalk is based on publish/subscribe architecture at its core. It means that BizTalk applications have to be activated by some type of external event (a receipt of a message). On the other hand SSIS packages can be scheduled.
Gravatar # re: BizTalk and SSIS
by Luke Kennedy at 1/29/2009 9:45 AM
BizTalk can be scheduled! All ports have the option of setting a a schedule.
Gravatar # re: BizTalk and SSIS
by NL at 5/29/2009 5:33 AM
Your comment about using SSIS and BizTalk to handle large file to processs tractions, this can be handled by BizTalk server, and that also efficiently and effectively.
Do you have any other kiiler reason where you can use SSIS and BizTalk server in combination and that would give you great advatage on BizTalk as alone
Gravatar # re: BizTalk and SSIS
by bharat sahani at 6/16/2009 8:24 AM
biztalk can handle large files also but it doesnt means that u can perform all the task of ssis(etl) in biztalk
they are 2 diferent products and u have to decide wht to use based on ur business requirement
if ur business requires large data with complex data clensing along with real time implementations plus integration to legecy system i suggest biztalk plus ssis is the right choice
Gravatar # re: BizTalk and SSIS
by Fernando Pires at 6/18/2010 2:08 PM
It could be a good idea to make SSIS handle FTP communication TO external partners.
If a FTP site is temporarely down a bunch of messages will be pilled up and this would create alot of job to re-send them not talking about all the error messages that will be created.
Also SSIS can upload multiple files in one FTP session Biztalk will create one session per file.
Gravatar # re: BizTalk and SSIS
by Mahmoud Meflah at 7/12/2010 1:46 PM
I used DTS (SQL Server 2000) and SSIS (2005, 2008) in the past, and I read some technical articles on BizTalk. I believe that the key advantage of BizTalk over SSIS is that it provides a more oriented Process Integration and Business Activity Management Framework.
Gravatar # re: BizTalk and SSIS
by Simon M at 10/10/2011 2:31 PM
As a cloud computing consultant, most of our projects involve large scale integration to legacy ERP systems (SAP, Oracle, AS400, etc). The (incorrect) use of BizTalk as an ETL tool is one of the most common mistakes we see and it can lead to a lot of pain if used in the wrong way.

BizTalk works best when acting as an ESB to any number of applications that need to talk in a series of frequent, near-realtime lightweight updates.

ETL tools are the earth movers of the EAI world and are much more suited to processing large source files, prepping data that requires intense processing or bandwidth.

I definitely like the idea of combining SSIS and BizTalk that you mentioned in your post. I could see that might work well.

If you're trying to integrate apps with large volumes of data and multi-step integration processes (e.g. truncate source table, read 15Gb CSV, transform to target, load via SOAP-based web service) then opt for something a proper ETL tool (e.g. Informatica, Pervasive, IBM Cast Iron, Jitterbit, Talend, etc).

Hope this helps someone somewhere avoid some pain! :-)
Post A Comment
Title:
Name:
Email:
Website:
Comment:
Verification: