Friends, I Need Your Inputs – XML Data to SQL server Parsing / Mapping

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › Friends, I Need Your Inputs – XML Data to SQL server Parsing / Mapping

Viewing 2 reply threads
  • Author
    Posts
    • #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.

       

       

       

    • #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.

       

    • #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!

      • #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.

         

         

Viewing 2 reply threads
  • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.