The Biztalk Blokes

Wednesday, 3 October 2018

BizTalk 2016 and Dynamic eConnect 2013 Adapter

Intro

In this post I'll tell you about our experiences using BizTalk 2016 and the Dynamics eConnect 2013 Adapter together. But to save you reading the entire article I can tell you straight away, this combination seems to work perfectly well with no obvious issues.

So, here's the environment:

BizTalk:

Windows Server 2016 64 bit running BizTalk Server Enterprise 2016 64 bit with Feature Pack 2

BizTalk SQL Server:

Windows Server 2016 64 bit running SQL Server 2016 Enterprise

Microsoft Dynamics GP:

Windows Server 2012 running Dynamics GP 2013

Microsoft Dynamics GP SQL Sever:

Windows Server 2012 running SQL Server 2012

Approach:

eConnect has a few pre-requisites such as MSXML4.0 and Dexterity Shared Runtime. We installed these using the 64 bit installer where possible. When installing eConnect, install the 64 version as the 32 bit version will not work on a 64 bit server. Add your adapter within BizTalk Administration Console and restart your host instances.

We have also created several smaller development and test servers including 32 bit only versions. Following this approach substituting 32 bit with 64 bit during installation works perfectly well too.

Scenarios:

We are using eConnect to integrate POP and SOP transactions which work well and successfully handle failed instances if eConnect encounters GP validation issues. Resuming after such issues also works well.

Caveats:

We have encountered a few minor configuration "teething" issues with this configuration. My next blog post will deal with these in detail.

https://thebtsblokes.blogspot.com/2018/10/biztalk-2016-64bit-and-dynamics-gp.html

BizTalk 2016 64bit and Dynamics GP eConnect 2016 64bit

Intro
As promised in an earlier post, there is a bit of a quirk to using the eConnect 2013 adapter in 64 bit mode.

Symptoms
You are using a BizTalk 2016 64 bit server and have installed the eConnect 2013 adapter 64 bit mode to the C:\Program Files\Microsoft Dynamics folder structure. You try to use the adapter to send a message to GP but receive the following exception:

The Messaging Engine failed to create the transport adapter "Dynamics GP eConnect".
  OutboundAssemblyPath: "C:\Program Files (x86)\Microsoft Dynamics\eConnect 12.0\Objects\DotNet\Microsoft.Dynamics.GP.eConnect.BizTalk.Adapter.dll"
  OutboundTypeName: "Microsoft.Dynamics.GP.eConnect.Biztalk.Transmitter"
  Exception Details: "Could not load file or assembly 'file:///C:\Program Files (x86)\Microsoft Dynamics\eConnect 12.0\Objects\DotNet\Microsoft.Dynamics.GP.eConnect.BizTalk.Adapter.dll' or one of its dependencies. An attempt was made to load a program with an incorrect format."

Cause
The key to this is the part of the exception which states "Could not load file or assembly 'file:///C:\Program Files (x86)\Microsoft Dynamics\eConnect 12.0\Objects\DotNet\Microsoft.Dynamics.GP.eConnect.BizTalk.Adapter.dll'" as this isn't where we installed the adapter.

There are two issues here. First, how do you tell BizTalk it's looking in the wrong folder? Second...

Hey! that DLL does exist in C:\Program Files (x86)\Microsoft Dynamics! Oh, and in C:\Program Files\Microsoft Dynamics as well!

It seems the 64 bit eConnect installer writes into both folders and presumably installs the 64 bit version in one and the 32 bit in the other. It then tries to use the 32 bit version from the 64 bit host instance and chokes.

Workaround
After some hacking through BizTalk and eConnect documentation and configuration it seems it's not easy to tell BizTalk which folder to look in. However it appears using the DLLs in C:\Program Files (x86)\Microsoft Dynamics is a viable option. In our case we simply created a dedicated host instance for 32 bit adapters and then configured the eConnect adapter to use this instance instead.

One point of note. After doing this and then restarting the host instances you will be unable to resume the suspended message instances. They will throw the following exception:

A message sent to adapter "BiztalkMessagingEngine" on send port "Post Outbound eConnectSalesInvoice Message to GP FNL" with URI "data source=GPDatabase;initial catalog=FNL;integrated security=SSPI;persist security info=False;packet size=4096" is suspended.
Error details: Unknown Error Description
MessageId: {DD3BC94A-6FC6-4B77-8871-3898DAB2CA89}
InstanceID: {50177942-B210-4BA0-AF27-323B8430B6E5}

While this exception looks unfriendly at first it is actually quite informative. The adpater called "BizTalkMessageEngine" is actually a kind of wrapper for all other adapters. What it's saying here is that the underlying adapter has failed and has not been able to send any exception details back.

Or, to put it another way, resuming the eConnect message using an adapter that has been entirely reconfigured to use a different processor architecture using different DLLs has been a fraction promlematic. Fair enough I guess. Terminate the message instances. The ones from this point on will work fine.

BizTalk 2016 64 Bit and SQL Adapter

Intro

In this post I'll tell you about our experiences using BizTalk 2016 64 bit and the standard SQL adapter together. It's not exactly rocket science but hopefully it may save a few people a few minutes.

So, here's the environment:

BizTalk:

Windows Server 2016 64 bit running BizTalk Server Enterprise 2016 64 bit with Feature Pack 2

BizTalk SQL Server:

Windows Server 2016 64 bit running SQL Server 2016 Enterprise

BizTalk Host Instances:

One 64 bit In-Process host

Scenario:

We are using the SQL adapter packaged with BizTalk Server to receive data from our SQL server using a stored procedure. When executing the query we receive the following error:

The receive location "Receive Blah" with URL "SQL://blahserver/blahdb/blahquery" is shutting down. Details:"The receive adapter on this receive location is not supported on 64-bit host instances. Please make sure that the receive adapter is associated with a 32-bit only handler. ".

Fix:

This is one of the few occassions when BizTalk gives you an exception which is short, explicit and gives you all the information you need. Simply create an extra host in BizTalk Administration Console which has the 32 bit checkbox checked. Add the host instance to go with it. Then go into the Adapers section and look at the properties of the SQL adapter. Change the default host to your new 32 bit host and then restart both host instances. Your adapter will now work perfectly.

Wednesday, 20 September 2017

Why Are My Integrations Running So Slow?

We've just resolved an issue with one of our production servers which is taking an extremely long time to integrate some very small, infrequent messages. The integration scenario is as follows:-

Source System

Exports .XML files of between 1k and 5k at a rate of about one file per minute.
The files contain all information needed in the destination system so no enrichment will be required.
The maximum node depth in the message is five deep.

Destination System

Inserts the data present in the XML files into two SQL Server 2005 database tables through a single stored procedure.
Although the tables have primary keys and other indexes and have around one million records the performance of the insert stored procedure indicates that insertion time should be around one second.

Integration

A file system receive port is monitoring for .XML files on the source server.
A WCF-Custom / SQL port is executing the stored procedure on the destination server.
The source system, destination system and BizTalk server are all on the same LAN which is performing well.
The source system and destination systems are performing well.
The integration writes the message to a file send location as an archive as well as processing the message through a simple orchestration to write to the destination system.
The integration normally executes as soon as the file is written and completes in one to two seconds.

Issue

The file is being created in the source system folder but is not read by BizTalk for up to twenty minutes.
No errors are reported in the application event log.
No running instances are created until the file is read.
No suspended instances are created.
Other than the time delay, no other problems exist.

So, how do we diagnose this?

First of all I needed to eliminate problems with the source folder. What if the file system event is not firing?

Well, the folder in question is a sub-folder of an overall "integration" share. Another BizTalk server is happily reading from other sub-folders in that share. Furthermore, the folder is also used by the source application to process incoming files. Files written to this folder are instantly read by the application, indicating that folder performance is unlikely to be an issue.

So, the next problem is, if the source file system is not at fault, why isn't BizTalk picking up the file and removing it?

My next thought is that maybe the source application is holding a lock on the file so BizTalk can't access the file until the lock has been released. It seems unlikely - BizTalk is logging no errors or warnings - but it's something I am lucky enough to be able to check by dropping my own .XML file into the folder. Sure enough it is not picked up by BizTalk either. It looks like the source application is off the hook on this one.

So, next I move my accusing finger to point to the destination application. If BizTalk is reporting no errors, perhaps the message is being delivered successfully but is not visible in the destination application. That this is not the case can be show in a number of ways...

1) The file hasn't left the source directory

2) The archive version of the file is not created

3) No records exist when querying the database using tSQL

4) A SQL profiler trace shows no activity at all from BizTalk

Yup, the destination system is off the hook too. This is a BizTalk issue. So why isn't BizTalk logging the problem?

Well, maybe BizTalk isn't logging the problem because it can't. Is our server out of disk space? Does it have any other issues?

A quick trawl through the event logs, through Windows Explorer and Task Manager show the server to be in tip-top condition. Checking BizTalk Admin console and the event logs also shows that it's not throttling either. It's just running very, very, very slowly.

So we've eliminated the source, the destination and the BizTalk server. What's left? The back end database server. Maybe it's out of space, is running slow, has a problem?

Again, a quick trawl through the event logs, through Windows Explorer and Task Manager show the server to be in tip-top condition. Opening SQL Server Management Studio takes a matter of moments and everything looks to be performing well.

Our mystery is almost at an end folks. We're just a couple of clicks away from the solution.

SQL Agent. Responsible for running the archiving, cleaning up and general performance enhancing jobs for the SQL back end of BizTalk. It's running OK, the service is started. It's a shame that the jobs themselves are all owned by the guy that built the server. Who left a while ago. Who had his AD account deleted a while ago!

Fixing the jobs took a matter of seconds, changing the owner to a much more suitable setting. Each job runs successfully as I watch. And flipping back to the source system I notice the backlog of files has already been cleared.

The lesson? What we do is hard. But we can make it much, much harder by doing the basics badly!!

Wednesday, 19 July 2017

ASP.NET MVC4 Web API Body Gotcha

If you use Visual Studio 2012 to create a new ASP.NET MVC4 Web API you will be able to create Controller classes pre-populated with stub methods for GET, POST, PUT and DELETE. They'll look something like this...

public HttpResponseMessage Put(string id, [FromBody]string value)
{
}

The trouble is, while they'll happily build and deploy they don't actually work in practice!

Why?

Because the [FromBody] attribute tells Web API to stream the HTTP body into the variable it's about to create. The default variable is "value" and is of type System.String. And Web API has no way to stream any HTTP content types into a string. To help further, some content types will throw an error saying no MediaTypeFormatter is present while other content types will simply process the body and send NULL into the variable.

The trick is to create a new MediaTypeFormatter which recognises the content type text/plain and streams it into a string. Create a new class in your project and add the following text...

using System;
using System.IO;
using System.Net.Http;
using System.Net.Http.Formatting;
using System.Net.Http.Headers;
using System.Threading.Tasks;

namespace RESTfulServices.Helpers
{
public class TextMediaTypeFormatter : MediaTypeFormatter
{
public TextMediaTypeFormatter()
{
SupportedMediaTypes.Add(new MediaTypeHeaderValue("text/xml"));
SupportedMediaTypes.Add(new MediaTypeHeaderValue("text/plain"));
SupportedMediaTypes.Add(new MediaTypeHeaderValue("text/javascript"));
}

public override Task<object> ReadFromStreamAsync(Type type, Stream readStream, HttpContent content, IFormatterLogger formatterLogger)
{
var taskCompletionSource = new TaskCompletionSource<object>();
try
{
var memoryStream = new MemoryStream();
readStream.CopyTo(memoryStream);
var s = System.Text.Encoding.UTF8.GetString(memoryStream.ToArray());
taskCompletionSource.SetResult(s);
}
catch (Exception e)
{
taskCompletionSource.SetException(e);
}
return taskCompletionSource.Task;
}

public override bool CanReadType(Type type)
{
return type == typeof(string);
}

public override bool CanWriteType(Type type)
{
return false;
}
}
}

Now you make this new formatter available to your API by adding the following line to the bottom of your Global.asax file...

GlobalConfiguration.Configuration.Formatters.Insert(0, new RESTfulServices.Helpers.TextMediaTypeFormatter());

All that's left to do is to send your HTTP message with the following header information...

Content-Type: text/plain

This will load the entire body into your "value" string variable, ready for you to parse as appropriate.

(Thanks to https://myadventuresincoding.wordpress.com/2012/06/19/c-supporting-textplain-in-an-mvc-4-rc-web-api-application/ for saving my vacation!)

Monday, 17 July 2017

Unit Testing SQL Databases

So in VS2015 it seems to create database projects at version 11.0 but it creates the database unit test project at version 14.0 which means the tests don't work during the build. The error you'll receive is...

Error Message:

Assembly Initialization method GPFacade.UnitTests.SqlDatabaseSetup.InitializeAssembly threw exception. Microsoft.Build.Exceptions.InvalidProjectFileException: Microsoft.Build.Exceptions.InvalidProjectFileException: The imported project "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the path in the <Import> declaration is correct, and that the file exists on disk. C:\Agent\_work\6\s\GPFacade\GPFacade.sqlproj. Aborting test execution.

Stack Trace:

at Microsoft.Build.Shared.ProjectErrorUtilities.ThrowInvalidProject(String errorSubCategoryResourceName, IElementLocation elementLocation, String resourceName, Object[] args)

at Microsoft.Build.Shared.ProjectErrorUtilities.ThrowInvalidProject(IElementLocation elementLocation, String resourceName, Object arg0)

at Microsoft.Build.Evaluation.Evaluator`4.ExpandAndLoadImports(String directoryOfImportingFile, String importExpressionEscaped, ProjectImportElement importElement)

at Microsoft.Build.Evaluation.Evaluator`4.EvaluateImportElement(String directoryOfImportingFile, ProjectImportElement importElement)

at Microsoft.Build.Evaluation.Evaluator`4.PerformDepthFirstPass(ProjectRootElement currentProjectOrImport)

at Microsoft.Build.Evaluation.Evaluator`4.Evaluate()

at Microsoft.Build.Evaluation.Evaluator`4.Evaluate(IEvaluatorData`4 data, ProjectRootElement root, ProjectLoadSettings loadSettings, Int32 maxNodeCount, PropertyDictionary`1 environmentProperties, ILoggingService loggingService, IItemFactory`2 itemFactory, IToolsetProvider toolsetProvider, ProjectRootElementCache projectRootElementCache, BuildEventContext buildEventContext, ProjectInstance projectInstanceIfAnyForDebuggerOnly)

at Microsoft.Build.Evaluation.Project.ReevaluateIfNecessary(ILoggingService loggingServiceForEvaluation)

at Microsoft.Build.Evaluation.Project.Initialize(IDictionary`2 globalProperties, String toolsVersion, String subToolsetVersion, ProjectLoadSettings loadSettings)

at Microsoft.Build.Evaluation.Project..ctor(String projectFile, IDictionary`2 globalProperties, String toolsVersion, String subToolsetVersion, ProjectCollection projectCollection, ProjectLoadSettings loadSettings)

at Microsoft.Data.Tools.Schema.Sql.UnitTesting.MSBuildWrapper.DeployDatabaseProject(FileInfo databaseProjectFile, String configuration, String targetDatabaseName, String connectionString, Boolean forceDeployment)

at Microsoft.Data.Tools.Schema.Sql.UnitTesting.SqlDatabaseTestService.DeployDatabaseProject(String databaseProjectFileName, String configuration, String providerInvariantName, String connectionString, Boolean forceDeployment)

at Microsoft.Data.Tools.Schema.Sql.UnitTesting.SqlDatabaseTestService.DeployDatabaseProject()

at GPFacade.UnitTests.SqlDatabaseSetup.InitializeAssembly(TestContext ctx) in C:\Agent\_work\6\s\GPFacade.UnitTests\SqlDatabaseSetup.cs:line 20

To get round this...

Right mouse click on the database project and select "Unload"
Right mouse click on the unloaded database project and select "Edit"
Search the file for a section of text which looks like this...

</PropertyGroup>

Replace the 11.0 with 14.0 and save the file
Right mouse click on the unloaded database project and select "Reload"
Commit your changes and check they now build

Thursday, 29 June 2017

Using IIS Publishing Packages With Visual Studio Online Builds

When you create a publishing profile for a web service in Visual Studio it generates an XML file while looks like this:-

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

<!--

This file is used by the publish/package process of your Web project. You can customize the behavior of this process

by editing this MSBuild file. In order to learn more about this please visit http://go.microsoft.com/fwlink/?LinkID=208121.

-->

<WebPublishMethod>Package</WebPublishMethod>

<LastUsedBuildConfiguration>Debug</LastUsedBuildConfiguration>

<DebugSymbols>False</DebugSymbols>

<WDPMergeOption>DonotMerge</WDPMergeOption>

<ExcludeApp_Data>False</ExcludeApp_Data>

<DesktopBuildPackageLocation>bin\MyWebService.zip</DesktopBuildPackageLocation>

<DeployIisAppPath>Default Web Site/MyWebService</DeployIisAppPath>

</Object>

</UpdateFrom>

</ObjectGroup>

</Objects>

</PublishDatabaseSettings>

</PropertyGroup>

</ItemGroup>

</Project>

This works well if you are deploying from within Visual Studio or importing an application in IIS.

However if you are setting up a Visual Studio Online (VSO) build and release you will need to make some extra modifications to ensure that the correct connections are used for your different environments.

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

<!--

This file is used by the publish/package process of your Web project. You can customize the behavior of this process

by editing this MSBuild file. In order to learn more about this please visit http://go.microsoft.com/fwlink/?LinkID=208121.

-->

<WebPublishMethod>Package</WebPublishMethod>

<LastUsedBuildConfiguration>Debug</LastUsedBuildConfiguration>

<DebugSymbols>False</DebugSymbols>

<WDPMergeOption>DonotMerge</WDPMergeOption>

<ExcludeApp_Data>False</ExcludeApp_Data>

<DesktopBuildPackageLocation>bin\MyWebService.zip</DesktopBuildPackageLocation>

<DeployIisAppPath>Default Web Site/MyWebService</DeployIisAppPath>

</Object>

</UpdateFrom>

</ObjectGroup>

</Objects>

</PublishDatabaseSettings>

</PropertyGroup>

<ParameterValue>Data Source=.;Initial Catalog=MyDatabase;Integrated Security=True</ParameterValue>

</MSDeployParameterValue>

</ItemGroup>

</Project>

In this version of the file we have explicitly set the MSDeployParameterValue for our connection which, as the name suggests, is going to be used by the MSDeploy utility to configure the web service when we release.