BTS: BizTalk 2006 to BizTalk 2010 migration: All about Legacy SQL Adapter and migrating it to WCF SQL Adapter

These steps needs to be performed once you have successfully migrated BizTalk 2006 solution to BizTalk 2010 solution.

• Legacy SQL Adapter is configured to
  1. Stored Procedure which has SELECT and INSERT statements.
  2. Update Gram INSERT 

Issue # 1

WCF SQL adapter: Need to change to Strongly Typed Stored Procedure in case the default one with DataSets / Array of Datasets is not required. Optional, the issue with this is that the SQL response gets changed completely. In case the migration tool is used since the tool does not give an option of selecting the output type which we otherwise get when we use a WCF Sql Adapter from scratch- Manual Step.

Resolution

The response message schema (Legacy Sql Adapter) is backward compatible with the WCF Adapter. Hence even though the Migration tool generates a brand new schema (multirooted - both for request as
well as response), it does not change the message type of the response message.

[Below change is applicable only in case the legacy response schema is backward compatible which happens only when the response contains a collection - Non-Insert]

A small change needs to be done in the SOAP Header section of the WCF Request/Response Port - Prefix the namespace with 'XML'

Issue # 2

Logical Port Operation name needs to be mapped to Operation name in the Binding Info

Resolution

Mandatory since the operation name of the logical port must match with the Operation name specified under the BtsActionMapping of Wcf Send Port - Manual Step.

Issue # 3

The Old schemas and the port types remain with the solution and can be removed if required

Resolution

To be done as per the project requirements and based on other dependencies.

Issue # 4

Create a new physical send port (WCF Custom - Sql Binding) by importing the bindings which have been added under the migration process.

Resolution

Import the bindings (customer one, generated by the tool itself and then make the necessary changes if any)

1. 1. 1. If any Sql response schema is being used in map as a source then ensure that both the "AttributeFormDefault" and "ElementFormDefault" should be set to Unqualified. This is because of a bug in WCF which results in blank
         namespace (xmlns="“) at the individual record level of the response which results in mapping failure.
      2. In case any map this involves more than one source or destination schema and if one of those multiple source or destinations schemas is the changed Sql schema, then the migration tool fails to update that map. This has to be done manually.

• Legacy SQL Adapter is configured to
  1. Polling Stored Procedure - SELECT

Issue # 1

The polling schema made available to the solution by Legacy SQL Adapter is backward compatible and hence can be used as it is by the WCF Sql Adapter.

But one issue which still persists and requires manual change (not specific to Migration tool as the issue is with the WCF Adapter) -

Records fetched by WCF Sql Adapter have blank XMLNS with every record unlike the legacy Sql Adapter where there are no such blank namespaces coming along. This results in validation failure wherever the polled schema is being used as a SOURCE SCHEMA.

Resolution

Additionally following 2 changes need to be made: 

1. 1. 1. Need to feed in the "PolledDataAvailableStatement" as it is a mandatory field before the polling operation could start.
      2. Marked "UseAmbientTransactions" as false to eliminate the error.
      3. For the blank namespace problems - the ElementFormDefault property needs to be set to 'False'

The credit for this goes to Amit Shuka (https://www.linkedin.com/in/shuklaamit)