WCF-SQL Adapter MSDTC / Identity Insert Issue

Fri Jul 3, 2009 by Aldrich in microsoft-sql-server, biztalk-tungkol-sa-biztalk

I was working on a Biztalk integration project that perform a CRUD operation using old SQL adapter (the new one is available via Microsoft Adapter Pack 2.0), everything works fine on the development environment but when we've tried to deploy it to test environment which have the same structure with the production server we stumble again on MSDTC issues.

To cut the story short it was too complicated and too many settings (firewall, NETBIOS etc) needs to be change since the database server is located on DMZ. The next day while I'm configuring the mySAP adapter, I found out that license has expired, since we've downloaded the trial version of BizTalk adapter pack 1.0. So I have to look for another setup, it so happen that for some reason I can't find the 1.0 version that's why I've installed the Adapter pack 2.0. I was a little bit surprise to see that there's a new SQL Adapter included. So I've installed it and give it a try and it works fine.

Two important sql binding settings that needs to be set are:

1) useAmbientTransaction = false, to solve MSDTC issue.

2) allowIdentityInsert = true, if you're trying to add rows to a table that has identity column.