January 14, 2008 at 9:17 am
Hello,
At our company we are considering building an architecture for file imports and processing and considering both Biztalk and SSIS at this time.
My understanding from reading the material out there regarding this subject that Biztalk is more suited for integrating applications and real time communication of information and SSIS is more suited for bulk loads into databases/data warehouses and data manipulation.
Currently we are somewhat along the lines described above, but there is a desire to use one technology over the other for importing files and data manipulation, and I am not sure that is practical. Also there is a debate currently as to whether Biztalk does better at handling logic than SSIS.
I have read through the article on microsoft site that outlines the above - http://www.microsoft.com/technet/prodtechnol/biztalk/2004/whitepapers/integration.mspx
However, I would like to get some input from people who have actually used both these tools in the real world for ETL process and could provide some insight so as to help us make an informed decision.
Thanks
KR
January 14, 2008 at 9:47 am
At my current company we use SSIS extensively and BizTalk a little. I have previously worked with BizTalk for a couple of years at another company. Here is my assessment:
BizTalk can do pretty much anything SSIS can - close to as well. SSIS is far easier to use, configure, and develop in.
If I had to decide on one or the other, the big think I would base my decision on is if I had processes in which I needed user intervention. For instance, if you get a new customer added to your CRM and you need to copy that customer record to your financial system but want to email a finance user and collect some additional banking information to insert with the record. This is something that BizTalk would be really good at and SSIS is not.
If my process just needed to copy the record to the finance system and then notify a user that a new record was added and they were to go add some additional information, I would lean toward SSIS because it would take me less time to set up and the administration would be easier (again, my opinion here).
It is these "pause and wait" type processes that BizTalk is far better for over SSIS. SSIS is a really good ETL tool (especially considering you get it with SQL Server) and BizTalk is a workflow and process management tool. My advice is to look at the processes you intend to build and decide if the overhead of BizTalk is worth the additional maintenance and development. If your processes are just: get some records, do some data manipulation, then load them into one or more destinations; I would recommend sticking to SSIS.
If your processes are split down the middle - don't be afraid to use both. For simple ETL processes and data warehouse loads, SSIS is great and ends up being pretty low maintenance. Even if you need BizTalk for something else, don't use it for something sinple.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply