BizTalk WCF-SQL Polling and de-batching on Receive Port(De-batching SQL Data)

Hi BizTalk Buddies,

Hope you are all doing great. Every one of us come across the scenario where we need to poll from the database. When we are polling, we can either can poll the single record at a time and process it or poll multiple records at a time and de-batch using Envelope schema on the receive port and Process the de-batched records individually. Here is a simple article that explains how to poll from the DB and de-batch on the receive port and subscribe to the Debatched Messages.

Below is the Employee Schema(Employee.xsd) that contains the Single Employee.
<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="http://BTS_SQL_Debatch.Employee" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" elementFormDefault="qualified" targetNamespace="http://BTS_SQL_Debatch.Employee" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Employee">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Name" type="xs:string" />
        <xs:element name="ID" type="xs:string" />
        <xs:element name="Salary" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>
Below is the Employees Schema(Employees.xsd) that contains multiple employees that are polled from the database. Employee schema is imported to this Schema. Employees Schema has marked as an Envelope and the Body XPath is selected as shown below.
<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="http://BTS_SQL_Debatch.Employees" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns:ns0="http://BTS_SQL_Debatch.Employee" targetNamespace="http://BTS_SQL_Debatch.Employees" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:import schemaLocation=".\Employee.xsd" namespace="http://BTS_SQL_Debatch.Employee" />
  <xs:annotation>
    <xs:appinfo>
      <b:schemaInfo is_envelope="yes" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" />
      <b:references>
        <b:reference targetNamespace="http://BTS_SQL_Debatch.Employee" />
      </b:references>
    </xs:appinfo>
  </xs:annotation>
  <xs:element name="Employees">
    <xs:annotation>
      <xs:appinfo>
        <b:recordInfo body_xpath="/*[local-name()='Employees' and namespace-uri()='http://BTS_SQL_Debatch.Employees']" />
      </xs:appinfo>
    </xs:annotation>
    <xs:complexType>
      <xs:sequence>
        <xs:element ref="ns0:Employee" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Deploy the solution.

Once the solution is been deployed, We need to create One-way WCF-SQL receive Port as shown below.
Port Name:- rcv_Employees
Receive Location Name:- rcv_WCF-SQL_Employees
Type:- WCF-SQL
Receive Pipeline:- XMLReceive

In the Configure section please do the below settings.
Address(URI):- mssql://YourServerName//YourDatabaseName?InboundId=getEmployees
XmlStoredProcedureRootNodeName:- Employees
XmlStoredProcedureRootNodeNamespace:- http://BTS_SQL_Debatch.Employees
InboundOperationType:- "XmlPolling"
PolledDataAvailableStatement:-  SELECT COUNT(1) FROM [dbo].[Employee] WHERE IsPolled IS NULL AND DatetimeBizTalkPolled IS NULL
PollingStatement:-   exec [dbo].[getEmployees]

Rest of the properties you can leave then to the default values.

Save the above configuration. 

Now Create a FILE Sendport that subscribes on BTS.MessageType="http://BTS_SQL_Debatch.Employee#Employee"

Below are the scripts to create the table and the stored proc.

--Table
CREATE TABLE [dbo].[Employee](
[Name] [varchar](50) NULL,
[ID] [int] NULL,
[Salary] [decimal](18, 2) NULL,
[PollingId] [uniqueidentifier] NULL,
[IsPolled] [bit] NULL,
[DatetimeBizTalkPolled] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Employee] ([Name], [ID], [Salary], [PollingId], [IsPolled], [DatetimeBizTalkPolled]) VALUES (N'Venu', 123, CAST(10.00 AS Decimal(18, 2)), NULL, NULL, NULL)
GO
INSERT [dbo].[Employee] ([Name], [ID], [Salary], [PollingId], [IsPolled], [DatetimeBizTalkPolled]) VALUES (N'Ravi', 124, CAST(34.00 AS Decimal(18, 2)), NULL, NULL, NULL)
GO
INSERT [dbo].[Employee] ([Name], [ID], [Salary], [PollingId], [IsPolled], [DatetimeBizTalkPolled]) VALUES (N'Raghu', 126, CAST(19.00 AS Decimal(18, 2)), NULL, NULL, NULL)
GO
INSERT [dbo].[Employee] ([Name], [ID], [Salary], [PollingId], [IsPolled], [DatetimeBizTalkPolled]) VALUES (N'Rakesh', 129, CAST(63.00 AS Decimal(18, 2)), NULL, NULL, NULL)
GO

Oracle Client Installation on BizTalk Server 2016

Hello BizTalk Buddies,

It has been a while that since I have written an article. 

Recently, I have had a requirement to upgrade BizTalk from 2010 to 2016. My client is using Oracle Database Adapter extensively in BizTalk Server.

There are good Articles that explain the installation and configuration or ODAC for BizTalk 2016 but I feel that they are not detail enough so it's my attempt to clear the gaps in understanding.

Before you can download the ODAC (Oracle Data Access Components), create an Oracle online account from the below URL.

https://login.oracle.com/mysso/signon.jsp

Once you have created an Oracle account, log in to oracle online and you can navigate to the below URL to download the 32 bit Version of the ODAC.

https://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html

Accept the License Agreement as shown below.

On the same page navigate down till you spot the 12.1.0.1.0 version of ODAC as shown below.

Logon to the BizTalk Server using either an Administrator's account or the service account that the host instances run on.

Open the command line as an administrator, and navigate to the folder location where you have extracted the downloaded zip file.

In my case, I have downloaded and extracted the file to the below location.

D:\Software\ODAC121010Xcopy_32bit

In the command prompt run the following command if you would like to install to C:\ Drive on the server. Otherwise, change the drive accordingly and run the below command.

D:\Software\Oracle Client\ODAC121010Xcopy_32bit>install.bat all C:\Oracle myhome

Note:- Some checks after the above installation is completed.

1). Double-check that, the Oracle home path is set up properly in System Environment variables.

2). If you are using TNS name aliases, then double-check your file exists in the respective location. In my case, it is in C:\Oracle\network\admin\tnsnames.ora.

Windows 10 October 2018 Update (version 1809) issue that is deleting the files

Hi all,

Microsoft October release for Windows 10 has some issues. You might find some missing files after the updated has been done.

Microsoft has officially confirmed that, Windows 10 users across the globe to not update Windows 10 October release(Release date: October 2, 2018) if you have not already updated. Below is the Microsoft URL that confirms the issue.

https://support.microsoft.com/en-us/help/4464619/windows-10-update-history
Below is the Screenshot taken from Microsoft support website.

If you have already updated the patches then don't don't start using the computer and immediately  contact your country Microsoft support number.

Below is the URL for respective country support numbers.

https://support.microsoft.com/en-us/help/4051701/global-customer-service-phone-numbers

Microsoft South Africa Contact Number:- 0860 22 55 67
Microsoft India Contact Numbers:- +91(0)80 4010 3000, 1 800 11 1100, 1 800 102 1100

Converting CDATA to XML and mapping it to the root node in BizTalk map using XSLT

In one of my recent projects, I’ve come across the requirement where the source system is sending the XML wrapped under the CDATA in string element.  Source system can send its own namespace in the content of the CDATA. I had a requirement to extract the CDATA and change the namespace in the CDATA and map it to the destination schema. Below is the procedure that I’ve followed to achieve the task.

There are 2 things that I have explored as part of this.

1). If the Source system doesn’t provide namespace

2). If Source System provides its own namespace and we need to change the source namespace to the destination namespace.

The Source and destination schemas that I have used to explain the concepts are shown below.

Source Schema CustomerCDATASchema.xsd

<?xml version="1.0" encoding="utf-16"?>

<xs:schema xmlns="http://BizTalkConcepts.CustomerCDATASchema" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" elementFormDefault="qualified" targetNamespace="http://BizTalkConcepts.CustomerCDATASchema" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <xs:element name="Customers">

    <xs:complexType>

      <xs:sequence>

        <xs:element name="CustomersCDATA" type="xs:string" />

      </xs:sequence>

    </xs:complexType>

  </xs:element>

</xs:schema>

Destination Schema CustomerXmlSchema.xsd

<?xml version="1.0" encoding="utf-16"?>

<xs:schema xmlns="http://BizTalkConcepts.CustomerXmlSchema" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" elementFormDefault="qualified" targetNamespace="http://BizTalkConcepts.CustomerXmlSchema" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <xs:element name="Customers">

    <xs:complexType>

      <xs:sequence>

        <xs:element name="Customer">

          <xs:complexType>

            <xs:sequence>

              <xs:element name="CustomerId" type="xs:string" />

              <xs:element name="CustomerName" type="xs:string" />

            </xs:sequence>

          </xs:complexType>

        </xs:element>

      </xs:sequence>

    </xs:complexType>

  </xs:element>

</xs:schema>

The above requirement can be achieved by using custom XSLT templates.

After Googling and spending some time I have managed to solve the problem.

The sample input message is as shown below.

<ns0:Customers xmlns:ns0="http://BizTalkConcepts.CustomerCDATASchema">

  <ns0:CustomersCDATA><![CDATA[

    <ns0:Customers xmlns:ns0="http://BizTalkConcepts.CustomerCDATASchema">

      <ns0:Customer>

        <ns0:CustomerId>CustomerId_0</ns0:CustomerId>

        <ns0:CustomerName>CustomerName_0</ns0:CustomerName>

      </ns0:Customer>

    </ns0:Customers>]]>

  </ns0:CustomersCDATA>

</ns0:Customers>

The Output that I’m expecting is as shown below. Make observation on the namespaces highlighted.

<ns0:Customers xmlns:ns0="http://BizTalkConcepts.CustomerXmlSchema">

  <ns0:Customer>

    <ns0:CustomerId>CustomerId_0</ns0:CustomerId>

    <ns0:CustomerName>CustomerName_0</ns0:CustomerName>

  </ns0:Customer>

</ns0:Customers>

Create a map CustomerCDATASchema_To_CustomerXmlSchema.btm.

This map takes the CDATA and converts to the XML.

Drag and drop the scripting functiod and select Script type as Inline XSLT Call Template, copy and paste the below code.

1.       If the Source system doesn’t provide namespace

<!-- This Template extracts the CDATA field from the Source and adds the namespace when outputting -->

<xsl:template name="TemplateToExtractCDATAandAddNamespace">

  <xsl:param name="param1" />

  <xsl:variable name="localCustomersCDATA" select="." />

  <xsl:call-template name="search-and-replace">

    <xsl:with-param name="input" select="string($localCustomersCDATA)" />

    <xsl:with-param name="search-string" select="string('&lt;Customers&gt;')" />

    <xsl:with-param name="replace-string" select="string('&lt;Customers xmlns=&quot;http://BizTalkConcepts.CustomerXmlSchema&quot;&gt;')" />

  </xsl:call-template>

</xsl:template>

<!-- This Template replaces the given string with the specified string. -->

<xsl:template name="search-and-replace">

  <xsl:param name="input"/>

  <xsl:param name="search-string"/>

  <xsl:param name="replace-string"/>

  <!-- Find the substring before the search string and store it in a

     variable -->

  <xsl:variable name="temp"

       select="substring-before($input,$search-string)"/>

  <xsl:choose>

    <!-- If $temp is not empty or the input starts with the search

          the string then we know we have to do a replace. This eliminates the

          need to use contains(  ). -->

    <xsl:when test="$temp or starts-with($input,$search-string)">

      <xsl:value-of disable-output-escaping="yes" select="concat($temp,$replace-string)"/>

      <xsl:call-template name="search-and-replace">

        <!-- We eliminate the need to call substring-after

                    by using the length of temp and the search string

                    to extract the remaining string in the recursive

                    call. -->

        <xsl:with-param name="input"

        select="substring($input,string-length($temp)+

                         string-length($search-string)+1)"/>

        <xsl:with-param name="search-string"

             select="$search-string"/>

        <xsl:with-param name="replace-string"

             select="$replace-string"/>

      </xsl:call-template>

    </xsl:when>

    <xsl:otherwise>

      <xsl:value-of disable-output-escaping="yes"  select="$input"/>

    </xsl:otherwise>

  </xsl:choose>

</xsl:template>

2.       If Source System provides its own namespace and we need to change the source namespace to the destination namespace.

If the Input that we are getting in the CDATA filed has source namespace that needs to be replaced with the destination namespace then just change the TemplateToExtractCDATAandAddNamespace template to the below shown then it will work. Make an observation to the highlighted section.

<xsl:template name="TemplateToExtractCDATAandAddNamespace">

  <xsl:param name="param1" />

  <xsl:variable name="localCustomersCDATA" select="." />

  <xsl:call-template name="search-and-replace">

    <xsl:with-param name="input" select="string($localCustomersCDATA)" />

    <xsl:with-param name="search-string" select="string(&apos;&quot;http://BizTalkConcepts.CustomerCDATASchema&quot;&apos;)" />

    <xsl:with-param name="replace-string" select="string(&apos;&quot;http://BizTalkConcepts.CustomerXmlSchema&quot;&apos;)" />

  </xsl:call-template>

</xsl:template>

Map the CustomersCDATA field to the Scripting template and the output of the scripting functiod to the root node in the destination as shown below.

NB: Please let me know if this article has helped you. Feel free to ask questions in the comments below if you have any requirements on the map that you are struggling to solve.