How to Upgrade BizTalk Send Port from SQL to WCF-SQL?

Upgrading your BizTalk application can be a daunting task, especially when transitioning from an SQL send port to a WCF-SQL send port. The primary concern here is whether you can utilize existing SQL schemas without having to regenerate them for the new WCF-SQL port. In this article, we will explore how to accomplish this while addressing some common issues, including SOAP Action headers and XML conformities that may occur during the transition.

Understanding the Transition to WCF-SQL

WCF-SQL provides a more flexible web service interface compared to traditional SQL send ports. While it offers enhanced functionality, such as support for multiple message formats and better error handling, transitioning requires careful attention to how schemas are managed. In your case, you want to leverage the existing schema related to your SQL scripts, particularly the following SQL execution script:

<xs:annotation>
  <xs:appinfo>
    <msbtssql:sqlScript xmlns:msbtssql="http://schemas.microsoft.com/xxxx/2003" value="exec [InsertUnitLoad] @Id=", @DateReg="", @DivisionCode="", @ODS="", @Reg=""/>
  </xs:appinfo>
</xs:annotation>

This schema is designed to execute the stored procedure InsertUnitLoad, passing various parameters. Maintaining this schema is crucial as it defines the structure of the message that will be sent to the WCF-SQL port.

Steps to Upgrade Your Send Port

Here are the detailed steps for upgrading from SQL to WCF-SQL while utilizing the old SQL schemas:

Step 1: Configure the WCF-SQL Port

1. Open your BizTalk Administration Console.
2. Navigate to the 'Ports' section and add a new WCF-SQL send port.
3. Configure the properties by selecting the appropriate binding (WCF-SQL) and specifying the connection string for the SQL Server.

Step 2: Set Up the SOAP Action Header

Setting the SOAP Action correctly is essential. In your case, use the following settings:

• SOAP Action: Procedure/dbo/InsertUnitLoad This tells the WCF-SQL endpoint which action to perform.

Step 3: Ensure XML Conformity

The error you encountered, stating that the start element with the name "Request" was unexpected, usually arises from an XML that does not align with the expected schema during the request.

• Fix XML Structure: Make sure the format of the XML you are sending conforms to the message structure defined in your existing schemas. Check the root element, namespaces, and child elements to ensure they match the expected definitions.

Example Request XML

Below is an example of what your request XML might look like:

<Request xmlns="BCA.Integration.Unit.SQL">
  <Id>123</Id>
  <DateReg>2023-01-01</DateReg>
  <DivisionCode>D001</DivisionCode>
  <ODS>ODS1</ODS>
  <Reg>REG1</Reg>
</Request>

Step 4: Testing and Iteration

After configuring the new WCF-SQL send port with proper XML structure and SOAP Action:

• Perform a test by sending a message to the WCF-SQL port.
• Monitor the BizTalk Admin Console for any errors or messages related to the WCF-SQL operations.

Common Issues to Look Out For

• Namespace Conflicts: Ensure that the XML follows the namespaces defined in your schema. If you have changes to the namespace, your XML will fail.
• Parameter Mismatches: Verify that the parameters you’re passing in the XML match those expected by the stored procedure. This includes correct data types and values.

Frequently Asked Questions (FAQ)

Can I use the old SQL schemas without modification?

Yes, it is possible to use old SQL schemas with WCF-SQL ports as long as the incoming XML aligns with the input schema of your WCF port.

What if my existing XML doesn’t work?

You may need to adjust your XML to match the expected structure of the WCF-SQL service. Ensure your elements are correctly named and namespaces are correctly referenced.

Is there a way to debug SOAP Action issues?

Use tools like SOAP UI or Postman to send requests to the WCF-SQL service and analyze responses or errors for more insights.

By following these steps, you can efficiently transition your BizTalk send port from SQL to WCF-SQL without needing to generate new schemas while ensuring adherence to XML structure.