On a recent engagement, I was developing a BizTalk 2013 solution that received XML-based data from several sources and ultimately inserted rows into a SQL Server database using a stored procedure. An additional requirement was that the database connection had to be dynamic.
As with most development, everything worked fine in the development environment and even in initial UAT testing. A timeout issue started to appear intermittently as the size of the data being processed increased and the content of the database grew. Rerunning the same test data through would often complete successfully. The details of the message logged to the Windows Logs was:
A message sent to adapter “WCF-Custom” on send port “WCF.Timeout_126.96.36.199_WCF.Timeout.ProcessDataToSQL_Dyn_WCFTest_c495b1996f35b303″ with URI “mssql://xxxx//Test?” is suspended.
Error details: System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. —> System.ComponentModel.Win32Exception…
Since SQL Server 2000, updategrams along with OPENXML have been available to insert, update, or delete data using XML documents. An updategram works against the XML views that are provided by the annotated XSD schema that contains the information required to map to the tables and columns to be modified. Using an updategram to manipulate one table in BizTalk is very straight forward because it integrates right into the Add Generated Items wizard for the SQL Adapter. Manipulating more than one table takes a little more effort because the wizard does not allow the user to select more than one table.
Multiple table insert updategram
There are two options to create an updategram in BizTalk for multiple tables; either manually or using the wizard to generate each individual table insert updategram and then manually merging them together. The next three figures…