CODEWITHSUNDEEP
biztalk
user1284940
user1284940

Reputation: 11

BizTalk WCF-SQL Composite Operation Send Port throwing Transmission Failure error

I am using CompositeOperation WCF-SQL Send port it works fine. Usually I was sending about 1000 records to a stored procedure that does upsert and it was working fine. Recently I received a file with about 5000 records and I am getting Transmission failure error. Is there any limit on how many records we can send using the compositeoperation port.
Since it creates one mega xml and add all the records as an xml node so I am wondering transmitting the large xml to sql from BizTalk may be not very efficient? How about if there are 500K records that needs to be sent to SQL using the Composite Operation WCS-SQL port. Is it advisable to use Composite Operation port? what are other possible options?

I have the following settings on the SendPort: Close Timeout: 00:01:00 Open Timeout: 00:01:00 Receive Timeout: Infinite Send Timeout: Infinite

Error details: System.Data.SqlClient.SqlException (0x80131904): The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

Trying to understand the root cause of the failure.

Upvotes: 0

Views: 279

Answers (1)

Dijkgraaf
Dijkgraaf

Reputation: 11527

I would suggest you should use a staging table and insert the data there, and set a flag somewhere in another table and have a SQL Agent Job that executes a stored procedure based on that flag that does the Merge. The insert should happen reasonably quickly, and the process intensive work will then not be taking place in a MSDTC transaction, as it will only involve the target DB and not the BizTalk DBs as well.

Upvotes: 0

Related Questions