none
WCF-SQL Adapter Bulk Inserts with Foreign Keys? RRS feed

  • Question

  • Hello all!

    I am trying to figure out the logical upgrade path from the legacy SQL adapter using updategrams.  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 ____?)

    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!

    Friday, September 11, 2009 8:08 PM

All replies

  • Hi,

    A similar suggestion I have already given in BizTalk Server General Forum. Now-a-days the design approach is that if you have "heavy duty" SQL Server usage in your BizTalk application, then you create a Data Access Layer component, with each data access object therein derived from ServicedComponent, being assumed that you imported System.EnterpriseServices namespace. Now that DAL component is registered with COM+ services using regsvcs utility if you have a multibox BizTalk environment using Active-Active or Active-Passive clusters. This improves performance manifold as compared to that of SQL adapters or updategrams and is suitable for the kind of scenario you are into.

    Thanks

    Ambar

    Sunday, September 13, 2009 8:04 AM
  • Interesting problem .. This is a possible solution ..

    Apparently  @@Identity  is not the path you want to go down .. because of the roadbloacks you already ran into ..

    What you need is 

    SELECT IDENT_CURRENT(’tablename’) // This would be your parent table


    It returns the last IDENTITY value produced in a table ( this ignores scope )

    But be warned .

    IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

    You can probably use that in your stored proc to update that value . provide your process is the only one inserting that table.

    Do update me on your results


    Happy BizTalking
    Monday, September 14, 2009 9:11 PM
  • Hi staken. Do you have a link to more information on the "10,000 variable exceed limit" for the SQL Adapter?

    Thanks,

    McGeeky
    Tuesday, September 15, 2009 12:09 PM