NZ Daylight Saving – String Datetime BizTalk Functoid

NZ Daylight Saving – String Datetime BizTalk Functoid

NZ daylight saving time ends at 3a.m today (2/3/2017) ends. To celebrate this I am going to share a string datetime functoid that I have used  for many years.

A common task when mapping one message to another is transforming a date e.g. 1/3/17 15:30 to 2017-03-01T15:30+13:00. Many years ago I was reviewing some maps in  a BizTalk solution and discovered that just about every map was using different C# code in a C# scripting functoid to transform between date formats. I decided to create a functoid that would convert any string that was a valid date to any other date format. The two objectives were to make sure that all date format changes were consistent and it was easy to use.

Here is are some maps that show cases the StringDateTime functoid. In this case the UTC date 2017-02-25T08:18:48.000Z is transformed to 25/02/2017 21:18

image

Note in the example above that the time is incremented by 13 hours because on the 25/2 NZ is on daylight saving and is UTC+13.

The code that does the work is shown below.

You can download the class file, bit map and resource file from here if you want to create the functoid yourself. Please see one of Sandro Pereira’s posts if you need any help to create it.

In summary this BizTalk functoid  gives consistency to the way dates are formatted and is easy to use. All you need to enter is the either a standard date format or a custom date format.

Global Integration Bootcamp 2017 NZ

Global Integration Bootcamp 2017 NZ

On Saturday 25 March, 2017 the Auckland Connected Systems User group held the NZ version of this global event. Saravana and Eldert have already summarised the global event. This blog will summarise the Auckland event only.

image

This is the third integration Saturday that ACUSG have run. The first event called “BizTalk Saturday” was organised by Nikolai Blackie and Bill Chesnut on 6th November 2010. The second event called “Integration Saturday” was held on July 18th 2015 once again had Bill Chesnut as the main speaker.

The organisers and presenters for the GIB 2017 event where Mark Brimble, Craig Haiden, Wagner Silveira, Nikolai Blackie, Mike Howell, Mahindra Morar, Morten Vellig, Abhishek Kumar and James Corbould.

014

Wagner said after the event “As the first team to go through the Global Integration Bootcamp 2017, I just wanted to say that it was an amazing experience. We ran from 8:30 am to 5:30 pm with an attendance of 30+ people and not a single dropout throughout the day. Everyone was quite interested in the presentations and tried the labs with real enthusiasm either individually or in groups. Actually was interesting to see a groups of people congregating around the same computer discussing, bouncing ideas, and thinking how to apply the knowledge on their own environment while doing the labs.A big shout out to the main organizers! I’m pretty sure I didn’t captured all of them here, but Eldert, Steef-Jan, Rob, please send my big thanks to them.”

Craig opened the event and then talked about API management.

006

Next up was Mahindra and Mike talked Service Bus, Enterprise Integration pack and On-Prem gateway.010After Lunch Abhishek walked us through Hybrid Integration.020He was followed by Morten who gave a compeliing demo of Power Apps and Flow.

023

Wagner bought the house down with a slick presentation about Logic Apps.

024James his partner in crime did an even slicker run through of Azure functions and Azure storage.

027Finally Nikolai blew ever one away with IOT.

GIB2017 - 5102

I had a really enjoyable day catching up with some old friends and just soaking up all the good integration vibrations. Thanks to all and I am looking forward to next years event.

Welcome to my new Blog

Welcome to my new Blog

My old blog at http://connectedpawns.wordpress.com is now being redirected to http://connectedpawns.com. All the old content is still there but it has been refreshed to a new more modern look. I thank Saravana Kumar and the BizTalk 360 team for sponsoring my blog and doing all the hard work to move my blog to its own domain.

The goals of my blog are;

  • Record my daily experiences with software integration.
  • Share want I am doing with other like minded people.
  • Communicate important issues.

Looking back at my old blog articles I am surprised that I have been writing now for more than 8 years. My first post is here on Sept 23, 2008. I enjoy writing and I find it cathartic after a bad day. I also find it useful to know where I have been and where I am going.

What can you expect from my blog going forward ? Over the coming weeks I am going to refresh some of the old blogs with better pictures. I will continue to write about integration, but this year I will write more about Azure integration as I get to grips with all this new stuff.

Below is picture of Spring outside where I live which is rather fitting way to end the announcement of the rebirth of my blog.

Pity the weather here is the pits at the moment. We have had more rain in one day than we normally have in one month , 121 milliliters.

Integrating Apache Active Message Queue(AMQ) with BizTalk Server – Publishing messages Part 3 (extra linefeed) workaround

Integrating Apache Active Message Queue(AMQ) with BizTalk Server – Publishing messages Part 3 (extra linefeed) workaround

In a previous blog I wrote about an issue when sending a message to an Apache AMQ using the REST API.  We decided introduce a workaround in the application that is using STOMP to retrieve the message. The workaround was to add custom property when we send the message to the AMQ like transport=rest. If the receiving application sees the transport property is rest it removes the linefeed. This is not ideal and we would still like to understand why the Apache AMQ REST API is adding an extra line feed to messages sent to the AMQ.

Integrating Apache Active Message Queue(AMQ) with BizTalk Server – Publishing messages Part 3 (extra linefeed) workaround

Integrating Apache Active Message Queue(AMQ) with BizTalk Server – Publishing messages Part 3 (extra linefeed) workaround

In a previous blog I wrote about an issue when sending a message to an Apache AMQ using the REST API.  We decided introduce a workaround in the application that is using STOMP to retrieve the message. The workaround was to add custom property when we send the message to the AMQ like transport=rest. If the receiving application sees the transport property is rest it removes the linefeed. This is not ideal and we would still like to understand why the Apache AMQ REST API is adding an extra line feed to messages sent to the AMQ.

Moving SQL 2016 BizTalk Databases that are in Availability Groups

Moving SQL 2016 BizTalk Databases that are in Availability Groups

Last week I had to move some databases quickly from one SQL server environment to another. Traditionally you would have built your new SQL servers, backup and restored your databases, user logins and SQL agent jobs and then run some VB scripts from your BizTalk Server to reconnect everything up (https://msdn.microsoft.com/en-us/library/aa547833(v=bts.20).aspx). This article describes another way to do this if you have chosen to use availability groups.

I had built a BizTalk 2016 on premise environment using SQL 2016 Availability groups for two talks that I did at Ignite NZ 2016 and on Integration Monday. I built this before Windows Server 2016 went RTM and had used the latest technical preview that was available at the time. I was preparing to put this into cold storage in case I wanted to demo this again but first I wanted to change to a supported version of Windows server. Windows Server 2016  Standard Technical Preview 5 expires this month and I tried upgrade to Windows Server 2016 but this was not allowed. I began to think about the traditional way of moving databases above but my brain began to hurt when I thought about how I was going to handle the availability groups etc but….then the light bulb went on Light bulb

btsql1

I decided to use SQL Always ON and failover to move my databases to a new server and then retire the old servers.

  1. I created two new Windows Server 2016 Standard Servers and joined them to the same Windows Failover Cluster that the Windows 2016 Server  TTP5 were part of.
  2. I installed SQL 2016 Developer Edition on these two new servers and installed the same SQL instances that I had on the TTP5 servers.
  3. I used a SQL service account for all instances and SQL server agents.
  4. I scripted all the logins and SQL agent jobs from the TTP5 server and applied these to the same instances on the new SQL servers. So now we had a new SQL servers like this without any BizTalk databases.                                         image
  5. On one of the SQL servers I had to open the registry and explicitly set the FQDN instance names for the SQL agents to run. e.g                                                                                                                                       image
  6. I copied all the firewall exclusions for the SQL instances and the SQL instance endpoints to the new servers.                                                                 image
  7. I created the SQL Endpoint listeners on each instance with a script like this                                                                                                                                                                                                                                                                                                                                                                                                                                                                            — =============================================
    — On both nodes, create an endpoint called AG1_endpoint that listens on TCP port **7022**
    — Run this script once on each node. PLEASE DO THIS OR ELSE YO WILL HAVE PAIN.
    — =============================================
    — Create endpoint on server instance that hosts the primary replica:
    CREATE ENDPOINT AGbts16sqlmgt_endpoint
    STATE=STARTED
    AS TCP (LISTENER_PORT=7023)
    FOR DATABASE_MIRRORING (ROLE=ALL)
    GO
  8. On each instance I added the two new replicas and started the synchronization. GOTCHA here: I had to remove one of my failover replicas before doing this because you can only have a maximum of three failover replicas. If you ignore this then all the steps work except you get an error like when you try to failover.                                                                                                  image
  9. Finally I failed over to one of the new servers , removed the old TTP5 servers and evicted them from the windows cluster. It was gratifying to see that BizTalk server kept running through out this process.
  10. I  had to add two linked servers so that the “BizTalk Backup’ SQL agent job would run if a failover occurred. If you don’t do this you get  “ Could not find server ‘bts16_listenerruntime’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.”

select name from sys.servers
EXEC sp_addlinkedserver @server=’bts16_listenerruntime’
EXEC sp_addlinkedserver @server=’bts16_listenertracking’
select name from sys.servers

What I have learnt from this experience is that you can use the Availability groups as a way of moving BizTalk database to new servers without much downtime and fuss. This also shows how one might carry out a DR test.

Integrating Apache Active Message Queue(AMQ) with BizTalk Server – Publishing messages Part 2 (extra linefeed)

Integrating Apache Active Message Queue(AMQ) with BizTalk Server – Publishing messages Part 2 (extra linefeed)

In previous blog I wrote about publishing a message to a AMQ. The documentation says “ActiveMQ implements a RESTful API to messaging which allows any web capable device to publish or consume messages using a regular HTTP POST or GET.”. In this article I want to share an issue that we have not been able to solve. A HTTP POST of a string “blah” to an Apache AMQ in our hands always adds an extra linefeed character i.e. “blahLF”.

We posted a positional flat file to an AMQ as shown below that does not contain any end of line(EOL) character.

clip_image002

We also used fiddler to confirm that the payload did not have a EOL character. The capture is shown below;

POST http://server01d:8161/api/message/MISC.MAX.DATA?type=queue&clientId=misc_data_biztalk&message_type=7228&message_version=1&branch_number=0&message_length=1268 HTTP/1.1
Content-Type: text/plain; charset=utf-8
Authorization: Basic bRlzY19kYXnotRealRhbGs6cUFUMUpLQ2dZUQ==
User-Agent: Microsoft (R) BizTalk (R) Server 2013 R2
Host: server01d:8161
Content-Length: 1268
Expect: 100-continue
Connection: Keep-Alive

T201701160700102017011620001020170116NTheGarbageseEftpos              NTWLPalmerstonNorth              NPN19.25     N1 Y                                                                Y            NNZDN483741……28  N0317N0317                            NL A/ARAVENA ARAVENA                                                                                                             000000032d028299NTXN100717                                                                       N00N00      NAPPROVED                                Y                N0000030170532858N9030200048573514    Y                                                                Y                                                                Y                                                                Y                                NSCR200          NPXULETSL_LIVE                   N895038         N89503801N4640      N146XA                           N4020618221          N0 NUnknown   NJY                                Y  YNZN281       Y                                                                Y                                Y                                N483741    NContactEMV                      N250       N3028      N755139225           N20170116NBEAEA6171CE0C6FF

When we retrieve the message from the queue using a HTTP GET or programmatically using STOMP we always get a line feed as an EOL character as shown below.

image

We believe it is the Apache REST API that is adding the additional LF character. Is this normal behaviour for the Apache AMQ REST API? We do not know how the REST API is implemented but if it is using the STOMP protocol it may be normal to add a line feed character. I’m wondering if the STOMP Frame is adding the LF.  I don’t know enough about the ActiveMQ to know for sure. See https://svn.apache.org/repos/asf/activemq/stomp/trunk/webgen/src/stomp10/specification.page which says;

We will use the augmented Backus-Naur Form (BNF) used in the HTTP/1.1 (rfc2616) to define a valid stomp frame:

LF                  =
CHAR                =
OCTET               =
DIGIT               =
NULL                =

frame-stream        = 1*frame

frame               = command LF
*( header LF )
LF
[ content ]
NULL
*( LF )

                          *( header LF )
                          LF
                          [ content ]
                          NULL
                          *( LF )

In summary we have found that the if we use Apache REST API to POST a message to a AMQ an extra line feed character is appended to the message. Does anyone know whether this is normal behaviour or is there a way to suppress this happening?

Can you use the BizTalk MLLP Adapter as a Socket Adapter?

Can you use the BizTalk MLLP Adapter as a Socket Adapter?

I have been doing  proof of concept where a application connects to BizTalk Server using a TCP/IP stream connection where the application will actively connect and BizTalk Server will listen for connections.

I have previously written a custom adapter called the GVCLC Adapter which actively connects to a vending machine. This was based on the Acme.Wcf.Lob.Socket Adapter example by Michael Stephenson. Another alternative that was considered was the Codeplex TCP/IP adapter. When I met Michael for the first time in Sydney two years ago I thanked him for his original post and he said to me why hadn’t I used the BizTalk MLLP adapter instead. In this post I examine whether it is possible to use MLLP adapter to connect to a socket.

The BizTalk MLLP adapter is part of the BizTalk HL7 accelerator. This is usually used with healthcare systems. The MLLP adapter at its essence is a socket adapter as shown by the receive and send port configurations shown below. can it be used for other non-healthcare systems?

imageimage

First I installed the BizTalk 2013 HL7 Accelerator with the minimal options to run the MLLP adapter. I had issues because you cannot install it if all the latest Windows Update have been installed but there is a workaround.

image

I setup a receive location as shown above once the MLLP adapter had been installed. Note i am not using any of the HL7 pipelines, no carriage return character, a custom start character and end character. Next I created a asynchronous socket test client very similar to the the Microsoft example here. I modified it so it would send a heart beat. I added the following lines at the right place

.//Start,End and ACK messages
protected const string STX = “02”;
protected const string ETX = “03”;
protected const string ENQ = “05”;

//…………………………….

// Send test data to the remote device.
//Send(client, “This is a test<EOF>”);
data = (char)Convert.ToInt32(STX, 16) + data + (char)Convert.ToInt32(ENQ, 16) + (char)Convert.ToInt32(ETX, 16);

Now after changing the host to 192.168.1.3, enabling the receive location and starting the socket test client, a ENQ message is received in BizTalk. This proves that the MLLP adapter can consume a socket client with a message that is not a HL7 message.

image

Next i set a file port to send a heartbeat message MLLP send adapter configure to send to 192.168.1.3 as above. The heartbeat message was <STX><ENQ><ETX>. I created a socket server similar to the Microsoft example here. I modified it be adding the following lines

//Start,End and ACK messages
protected const string STX = “02”;
protected const string ETX = “03”;
protected const string ACK = “06”;

//————–

// Echo the data back to the client.
//Send(handler, content);
// Send ACK message back to clinet
data = (char)Convert.ToInt32(STX, 16) + data + (char)Convert.ToInt32(ACK, 16) + (char)Convert.ToInt32(ETX, 16);

On dropping the file in the file receive port it was sent to the MLLP adapter and then sent to the socket server as shown by the printout below. There is five bytes because the configured adapter is also adding an extra STX and ETX character the message.

image

This proves the a MLLP receive adapter can consume messages from a socket client and that a send MLLP adapter can send to a socket server.

We have shown that MLLP adapter can be used instead of the Codeplex TCP/IP adapter or the Acme socket adapter

…….but this is not the end for me. My application wants to be a socket client and for BizTalk to send a heartbeat when it will send an AC i.e.

Application –>connect to BizTalk Server

BizTalk Server –>Message or heartbeat to the application

Application –> ACK message to BizTalk Server

I think I will have to create a custom adapter to do this.

In summary for the basic case the MLLP adapter can be used instead of the Codeplex TCP/IP adapter or the Acme socket adapter.

Integrating Apache ActiveMQ with BizTalk Server – Publishing messages

Integrating Apache ActiveMQ with BizTalk Server – Publishing messages

This blog will describe how we can publish a message using Biztalk server and the Apache ActiveMQ Rest API.  In previous blog I wrote about consuming a message from a AMQ and in this article show how to do it the other way.

To begin with we tried to use a BizTalk WCF-WebHTTP adapter to POST a message to the AMQ  with a request URL like http://servername:8161/api/message/MISC.MAX.DATA?type=queue&clientId=misc_data_biztalk&message_type=7222&message_version=2. We were pleased to see the message added to the queue with the correct properties. Unfortunately we got a transmission failure like “System.ServiceModel.ProtocolException: An HTTP Content-Type header is required for SOAP messaging and none was found” in the BizTalk group hub even though the message was added to the queue successfully. If we tried the request using fiddler it showed that the response does not contain a Content-Type in the header as well.

image

Getting a transmission failure each time BizTalk posts a message is not acceptable and we searched for a solution. We created a custom behaviour to add this a Content type to the header of the response but the WCF-WebHTTP adapter transmission failure occurs before the message gets to that point. Next we tried to configure the AMQ to add a content type but could not find the correct “config” to edit.

Finally we decided to choose BizTalk HTTP adapter instead of the WCF-WebHTTP adapter to POST the message to the AMQ. The HTTP adapter is a very old adapter and I reasoned that it might not be as picky about what is in the header of the response. It was pleasing to find that the HTTP adapter did  POST a message to the AMQ without a transmission failure. This is a good example of where the HTTP adapter can do something that the modern WCF-WebHTTP adapter cannot do. Thanks to Deepa Kamalanathan for proving all this.

imageclip_image002

In summary we have now shown that you can consume messages from a Apache AMQ with the BizTalk WebHTTP adapter and that you can publish messages to the AMQ with the HTTP adapter. One question remains and that is what throughput can be achieved using these adapters.