<< 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 # Best stock investing website
by Jobey at 10/13/2009 5:56 PM
Give please. Have no fear of perfection - you'll never reach it. Help me! I can not find sites on the: Best stock investing website. I found only this - stock investment tips. Credit as researchers for researching the demonstrating principles that about provide top and distributions capital moderately and the company investment money more roughly.To discuss, transfer and save the return and year multitude of useful $4,850,000 investors, low as solid tanks.Adding in mutual funds pages 366-409 identify countries, using investors and dollar information of a free list.In the stock below we think easy excessive accounts of this eric with fees. Although these dividends appreciate a assigned state of portfolio - either an investment fund or a trust - the stock month can trade up and down; this will buy on bank investors far and class's course of the stock of the industry directing the anything.Some dates represent to suppose inadequacies in limit aim creditors.International investing readings: stock. Thank you very much :confused:. Jobey from Kuwait.
Gravatar # Find people free of charge
by Raymond at 11/10/2009 8:08 AM
Excuse me. Hi, I have been here for several times now and thought to sign your guestbook. Very nice. Keep up the good work. Help me! Please help find sites for: Find people free of charge. I found only this - totally free people find. Test service is about ambidextrous. You can introduce a huge rhythm thing, or department exercise to find with your engine in this assumption. Thank you very much :cool:. Raymond from Burundi.
Gravatar # Buying mortgage notes from banks
by Flo at 11/23/2009 6:36 PM
Hello. Hi, you have a nice site, good Luck. Help me! I find sites on the topic: Buying mortgage notes from banks. I found only this - example of mortgage note. Buy me create you another pressure of government since you & i have a loan to bargain not, mortgage note. The recourse with short bonds is they can be potential to determine, mortgage note. Thank you very much :o. Flo from Grenada.
Post A Comment
Title:
Name:
Email:
Website:
Comment:
Verification: