Home Page › Forums › BizTalk 2004 – BizTalk 2010 › Friends, I Need Your Inputs – XML Data to SQL server Parsing / Mapping
- This topic has 3 replies, 1 voice, and was last updated 9 years, 10 months ago by
community-content.
-
AuthorPosts
-
-
June 25, 2008 at 10:09 PM #19990
Friends:
I am a solution Architect from Toronto, Canada.
After various options I have proposed an Integration Solution to my Customer where data feeds as XML files will
arrive from Various locations and I need to Map/Upload/Update the XML data to a SQL Server on a hourly/daily basis.
I included MSMQ as the Input Source to this Interface where all the XML’s will be posted by senders (Just a private Queue)
From here BizTalk needs to pickup the XML as they come and Parse it to the SQL Server and perform
Data Insert / Update as required based on the table that i will be mapping to.
can some one give me a direction on how to go ahead in building this kind of integration Solution using BizTalk 2006 R2
Note: The volume of XML’s coming in will be high, hence need to be scalable.
Any inputs from the Group is greatly appreciated.,kind of urgent !
Thanks in advance, Nagin.
-
June 26, 2008 at 3:57 AM #19995
This might be heresy on this forum, but Biztalk might not be the best solution to this problem.
Biztalk is not really designed for high volume ETL processes, you might be better using SQL Integration Services, SSIS. -
June 26, 2008 at 8:33 AM #19998
I agree. SSIS is stronger than BizTalk for high volume ETL. It is a very powerful tool when it comes to bulk loading data to and from database tables and flat files. However, BizTalk is better for working with different data formats and for transforming data. Also, BizTalk is better for continuous, event-driven integration. SSIS does not really support event-driven integration. You can do continuous integration with SSIS, but you need to schedule a package to run frequently.
Since we are considering different platforms here, I would consider WCF/WF/LINQ. I tend to lean toward this type of solution over SSIS or BizTalk to build data driven applications. Complex insert/update logic can become cumbersome in BizTalk, especially if you are making multiple database roundtrips. Also, the BizTalk SQL adapter has been known to deadlock under high transaction volumes. I like to use BizTalk as a tool to integrate applications rather than a tool to write applications. That said, you might want to encapsulate the database insert/update logic behind a WCF service, and front the service with BizTalk in an ESB style deployment.
Also, do you really need to use MSMQ? I believe it has a low message size limit ( somewhere in the < 5Mb range). You can accept the XML through a load balanced WCF service over http or tcp and still get high availability, probably better performance, and no size limit.
Hope this helps!
-
June 26, 2008 at 8:52 AM #19999
Many thanks for the quick replies and inputs.
I agree, MSMQ need not be there and I am considering WCF or even a simple FTP based process.
Coming to SSIS I have mixed opinions about its performance for doing Data Loads and ETL kind of stuff. I got feedback that SSIS is very slow.
One of the Reasons for using BizTalk is there will be various other External Systems(like ERP, CRM, other CSV feeds) which will have to communicate in a similar way to upload data. Hence I have proposed a EAI/Middleware system rather than a SSIS kind if solution.
Also as the other technologies in the Portal application area around Microsoft .NET I thought BizTalk will be a good Fit as an EAI tool which can do the Data Upload now and also help them Integrate will other external entities later in the Road.
LINQ is pretty much new and I have not explored it much but certainly will consider before I go forward with BizTalk.Any further inputs on LINQ which can sole my kind of problem is appreciated.Very much appreciate your Inputs and thanks once again.
Nagin.
-
-
-
AuthorPosts
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.