具有外键的WCF-SQL适配器批量插入？ [英] WCF-SQL Adapter Bulk Inserts with Foreign Keys?

问题描述

大家好！

我正在尝试使用updategrams从遗留SQL适配器中找出逻辑升级路径。最近，我被要求解决当前SQL发送端口的问题，该端口使用updategram将父子记录作为批处理插入到SQL Server 2005表中的一个事务中。遗留SQL适配器与updategram配合使用，除了非常大的文件，我们得到臭名昭着的10,000变量超限（因为标识外键变量，如下面的帖子......）http://blogs.tallan.com/biztalk/2009/02/10/updategram-with-multiple -tables-with-foreign-keys

经过研究，似乎更好的方法是使用Adapter Pack 2.0中新的和改进的WCF-SQL适配器。我现在正在尝试配置此适配器以执行与在sql server端执行插入的上述SQL适配器相同的操作（不是聊天并支持回滚）。

我试过以下配置仍然无法弄清楚如何将父表中的Identity列转换为MULTIPLE子表记录。我强调多次因为我使用了@@ IDENTITY的存储过程来插入外键值（只对一个子记录使用一条父记录）。

1。 CompositOperation包含对每个表的插入 - 不知道如何获取外键并将其分配给地图，就像我使用Updategram一样。显然，新的wcf-adapters不支持updategram。

2。使用@@ Identity函数的存储过程的CompositeOperation。如上所述，这只是一对一的。

我从以下链接开始遵循指南：

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 （这个使用脚本函数生成随机数... .. ____怎么了？）

http://msdn.microsoft.com/en-us/library/dd787894（BTS.10）.aspx

我的数据结构类似于...一个货件记录有很多订单

无论如何，这似乎是sc的共同点enario有这么多麻烦.... !!!也许我应该回到最初的SQL Adapter实现并找出解决方法（但它需要在一个事务中）

任何帮助将不胜感激！

解决方案

我在BizTalk Server General Forum中已经给出了类似的建议。现在的设计方法是，如果你有"重型"的话。在BizTalk应用程序中使用SQL Server，然后创建数据访问层组件，其中每个数据访问对象都派生自ServicedComponent，假定您导入了System.EnterpriseServices名称空间。现在，如果您使用主动 - 主动或主动 - 被动群集的多箱BizTalk环境，则使用regsvcs实用程序向COM +服务注册DAL组件。与SQL适配器或更新程序相比，这可以提高性能，适用于您所使用的场景。

谢谢

Ambar

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!

解决方案

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

这篇关于具有外键的WCF-SQL适配器批量插入？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！