WCF-SQL Adapter Bulk Inserts with Foreign Keys?

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › WCF-SQL Adapter Bulk Inserts with Foreign Keys?

Viewing 1 reply thread
  • Author
    Posts
    • September 11, 2009 at 1:12 PM #23229
      community-content

        Hello all!

        Recently, I was asked to address an issue with our current SQL send port that uses an updategram to insert parent child records as a batch and in one transaction into SQL Server 2005 tables.  The legacy SQL Adapter works great with the updategram except for really large files and we get the infamous 10,000 variable exceed limit (because of the identity foreign key variables like the following post…)http://blogs.tallan.com/biztalk/2009/02/10/updategram-with-multiple-tables-with-foreign-keys

        After researching, it seemed a better approach was to use the new and improved WCF-SQL adapter from the Adapter Pack 2.0.  I am now trying to configure this adapter to perform the same as the above SQL adapter executing the insertions on the sql server side (not chatty and to support roll-backs).

        I have tried the following configurations and still cannot figure out how to get the Identity column from the parent table into MULTIPLE child table records.  I stress multiple because I have used a stored procedure with the @@IDENTITY to insert the foreign key value (works only one parent record to one child record). 

        1. CompositOperation with Inserts to each table – not sure how to get the foreign key and assign it in the map like I did with the Updategram.  Apparently, the updategram is not supported with the new wcf-adapters.

        2. CompositeOperation with Stored Procedure using the @@Identity function.  This works only one-to-one as mentioned above.

        I have followed guidence from the following links:


        http://connectedthoughts.wordpress.com/2009/06/29/using-the-biztalk-wcf-sql-adapter-to-load-a-flat-file-into-a-sql-server-2008-table/

        http://www.packtpub.com/article/soa-capabilities-in-bizTalk-wcf   (this one using a script function to generate random numbers….. what’s up with that crap?)

        http://msdn.microsoft.com/en-us/library/dd787894(BTS.10).aspx

         

        My data structure is something like… one shipment record has many orders

        Anyway, this seems way to common of a scenario to have this much trouble….!!!  Maybe I should go back to the original SQL Adapter implementation and figure out a work around (but it needs to be in one transaction)

        Any help would be greatly appreciated!

      • September 11, 2009 at 5:07 PM #23230
        community-content

          One  method is to construct an Xml message with all records and pass this to a stored procedure as an Xml parameter. Inside the stored procedure, shred the Xml and apply your inserts. SQL does all the heavy lifting so it is fast. All inserts/updates occur in a single transaction.

          Here is an old example from Leonid Ganeline that uses OPENXML to shred the Xml in the stored proc.
          http://geekswithblogs.net/LeonidGaneline/archive/2008/04/03/biztalk-sql-port-how-to-pass-several-rows-to-the.aspx

          With SQL 2005 using XQuery is much faster to shred your Xml
          http://articles.techrepublic.com.com/5100-10878_11-6140404.html

          You have to pass your Xml as a string in an attribute of your stored procedure request message. Leonid’s example uses a distinguished field assignment to do this but you can also do it in a custom Xslt, with an external C# routine.

          • September 13, 2009 at 8:48 PM #23234
            community-content

              Thanks Greg!!  This seems like the best approach I have tried and researched thus far.  I get the stored procedure part about passing the records as XML and then shredding for the inserts.  I should be able to use Scope_Identity or @@Identity to solve my foreign key issues which is great.

              I’m a little confused on how to assign the stored procedure parameter from BizTalk.  Right now, I have a map that represents the canonical schema I want to insert.  What field is Leonid making distinguished and I’m assuming that is the field that is passed as the stored procedure parameter?

              Thanks again….

              • September 13, 2009 at 11:10 PM #23235
                community-content

                  if your stored proc signature looks like this:
                  bts_Load_XmlData @loaddata XML

                  Then your message will look like this: 
                  <Request xmlns=”yournamespace”>
                  <bts_Load_XmlData loaddata=”<escaped Xml as a string>” />
                  </Request>

                  When you generate the schema for the stored proc it will only contain a single attribute for the loaddata. You will need to create a schema describing the loaddata manually so that it matches your XQuery commands inside the stored proc.

                  You can create a message for the load data using a map from your source message. I would imagine you have done this already.

                  Then in a message assignment shape inside an orchestration you can construct the stored procedure message. You will need to make the loaddata attribute a distinguished field.

                  xmlDocumentVariable.LoadXml(“<Request xmlns=’yournamespace’><bts_Load_XmlData loaddata=” /></Request>”);
                  storedProcMessage = xmlDocumentVariable;

                  xmlDocumentVariable = LoadDataMessage;
                  storedProcMessage.bts_Load_XmlData.loaddata = xmlDocumentVariable.OuterXml;

                  Now send this to your SQL Send port.

                  You can create you store proc message in a single map, but you will need to use custom Xslt and a small external C# assembly.

                  • September 14, 2009 at 7:54 AM #23237
                    community-content

                      This makes a lot of sense – thanks for the clarification Greg!  Ok, I am going to work on a proof of concept today with the aproach you have described.  Thanks again!!

                       

                       

                • September 14, 2009 at 2:54 PM #23243
                  community-content

                    Greg, I wanted to let you know this is working for me great so far for the most part.  I still have to test a very large file and performance benchmark it, but I suspect it won’t have the 10,000 variable limit issue that the Updategram has since it’s being passed all as XML.

                    I did run into one issue – when the orchestration passes the sql parm it’s including the namespace.  I was able to get my stored procedure to work by manually executing it without the namespace.  I am not sure if my constructed message is incorrect in the orchestration or my XQuery should work around the namespace?

                    So my XML parameter passed =
                    ‘<nso:MyOrder xmlns:nso=http://my namespace.load_schema”>
                        <Orders>
                            <Order>
                                <PONum>123</PONum>
                            </Order>
                        <Orders>
                    </MyOrder>

                    And my sproc looks something like this…
                    INSERT INTO dbo.Orders
                     (
                          PONumber   
                     )   
                      SELECT
                       Table1.Column1.value(‘PONum[1]’, ‘CHAR(1)’)
                      FROM
                       @LoadXMLData.nodes(‘/Orders/Order’) AS Table1(Column1) 

                    When i manually remove the namespace, it works with just this XML…
                        <Orders>
                            <Order>
                                <PONum>123</PONum>
                            </Order>
                        <Orders>

                    • September 14, 2009 at 4:01 PM #23244
                      community-content

                        Option1.
                        Change your Orders schema and the map that creates this message.
                        In the schema remove the MyOrder node, so that  Orders is the root node. Also select the <schema> node and set the TragetNamespace property to blank. Unfortunately this will affect your map.

                        Option 2,
                        Change the XQuery in your stored procedure:

                        WITH XMLNAMESPACES( ‘http://my namespace.load_schema’ AS  “ns0”)
                        SELECT
                           Table1.Column1.value(‘PONum[1]’, ‘CHAR(1)’)
                          FROM
                           @LoadXMLData.nodes(‘/ns0:MyOrder/Orders/Order’) AS Table1(Column1) 

                • Author
                  Posts
                Viewing 1 reply thread
                • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.