We had a project in which there was a requirement for an integration between Microsoft SQL server and Microsoft Dynamics CRM 2011 using BizTalk 2013 R2, the solution in brief was Listening on SQL server using the WCF-Custom Adapter then using the orchestration to do data mapping then send the new transformed message to a custom WCF service that handles inserting the message as a new case in CRM, below are the hard learned lessons for such integration:
- When receiving message from SQL server with a specific schema, make sure the SQL statement is returning xml results matching the same schema defined in the orchestration or the mapping process will not be able of transforming the message to the target schema.
- When constructing a message using message assignment in an orchestration, check the message in the constructed messages property in the construct shape to resolve the build time error in case trying to assign value to a message not constructed from a receive shape.
- When configuring the WCF-Custom SQL binding connection, if there is only one default instance on the SQL server then you need to use the below format with double slash between the server name and the database name for the URI connection string: mssql://ServerName//DatabaseName
- When using the WCf-Custom adapter, there is an important property named UseAmbientTransaction which is defaulted to true, this property specifies whether the adapter will use DTC transactions in the communication with SQL server or use normal queries that doesn’t require DTC, you need to make sure that DTC is enabled on SQL server and is working properly by using a tool named DTCPing, sometimes the adapter will not work properly while not throwing any errors on the BizTalk server due to DTC issues like DNS name resolution that is blocking the transaction only on the SQL server side.
The experience with this integration was fruitful, interesting, and leveraged how BizTalk can be used to build robust and powerful integration solutions.