Processing Excel files in Biztalk

Using the ‘Microsoft OpenXml SDK’

http://www.dispatchertimer.com/tutorial/how-to-create-an-excel-file-in-net-using-openxml-part-1-basics/

https://docs.microsoft.com/en-us/office/open-xml/spreadsheets

Main download

Older method using ODBC etc

https://social.msdn.microsoft.com/Forums/en-US/5ce981f2-d5b7-41f6-b553-c717c565529d/reading-and-processing-excel-file-xlsx-in-biztalk-2010?forum=biztalkgeneral

https://social.msdn.microsoft.com/Forums/en-US/477c7de5-9f0c-4e84-b006-71c8cced2295/reading-excel-file-in-biztalk?forum=biztalkgeneral

http://biztalk.stottcreations.com/2007/12/biztalk_excel_xls_pipeline_component/

Subversion – check state of local work copy

Using “SlikSvn” (and most other svn clients), you can relatively easily check the state of your locally checked out copy on a Windows pc.

  • Are some objects not yet committed
  • Are some objects not yet added to Subversion

Add this to a .CMD file in Windows. In the output it will list objects that needs to be handled – most likely:

  • Commit Add/Change to Subversion
  • Release lock
CheckSVNState.CMD
——————————-
@ECHO OFF
SET DT=%DATE: =0%
SET TT=%TIME: =0%

SET OUTFILE=EDI_Local_Svn_%DT:~-4%%DT:~3,2%%DT:~0,2%.txt
C:\bin\SlikSvn\bin\svn status S:\SVNLocal | FIND /V “? ” > c:\temp\%OUTFILE%
——————————-

You can also check for objects that should have been added to Subversion, but have been missed:

CheckSVNMissingAdd.CMD
——————————-
@ECHO OFF

REM
REM Find all files in a given local working-copy that are NOT under version-control
REM

C:\bin\SlikSvn\bin\svn status S:\SVNLocal | find “? ” | find /v “\bin” | find /v “\obj” | find /v “.vs” | find /v “btproj.user” | find /v “csproj.user” | find /v “\UpgradeLog” | find /v “.btp.cs” | find /v “.xsd.cs” | find /v “.btm.cs” | more
——————————-

The files excluded by the various FIND commands needs to be adapted to the type of files you work with. The command above was create for Biztalk development.

Inspired by SO

Limit number of simultaneous connections to a WCF host

General method (works also for HTTP)

BizTalk MaxConnections

BizTalk enables users to configure WCF host to limit the maximum connections to a particular address.

The connectionManagement element in the btsntsvc.config file is designed to specify the number of connections to a given address. By default BizTalk allows for 2 connections for any given WCF host.

How to limit number of connections

https://stackoverflow.com/questions/3591283/biztalk-how-to-limit-number-of-connections-to-a-wcf-service

Throttling

https://docs.microsoft.com/en-us/biztalk/technical-guides/optimizing-biztalk-server-wcf-adapter-performance

Add the ServiceThrottlingBehavior service behavior to a WCF-Custom or WCF-CustomIsolated Receive Location and use the following settings:

Compilation of Biztalk graphical map results in OutOfMemory

After upgrading our UBL schemas from 2.0 to 2.1, our maps utilizing that schema would not compile any more. All of them – except one – would build for a long-long time, and would finally result in an OutOfMemoryException. The map that was not affected, was a pure XSLT map.

The UBL schemas have now gotten so big, that Visual Studio can generate an OutOfMemory while building a Biztalk graphical map (pure XSLT maps are not affected). It also takes quite some time before the OutOfMemory occurs – in the mean while, you can see the VS process continually allocating memory.

Open the .BTM file in Notepad, and change the GenerateDefaultFixedNodes attribute from “Yes” to “No“.

Save and build again

Ref: Biztalk map – Infinite loop

Cannot add resource to Biztalk – No .CLB data in the memory or stream

We are running a Biztalk 2016 env., and here I have experienced issues when adding a BiztalkAssembly to Biztalk using the rightclick + “Add Resource” on an Application in the Biztalk Admin Console. It gives this error:

It currently only occurs with a single DLL, containing a schema, where there are LOTS of maps that Biztalk want to remove (and redeploy afterwards) before adding this schema.

It gives exactly the same error, if I try to add the resource using BTSTASK

BTSTASK AddResource -ApplicationName:"Object.Invoice" -Type:"System.BizTalk:BizTalkAssembly" -OverWrite -Options:GacOnAdd,GacOnImport,GacOnInstall -Server:biztalkdev01\dbinst01 -Database:BizTalkMgmtDb -Source:c:\dll\BizTalk.Object.Invoice.Schemas.CDM_Invoice.dll

It also makes no difference, if I run the BTSTASK in a standard CMD or as “Administrator”

The only thing that works, is if I add the resource from another directory, than the one I added it from the previous time. In the example above, it works, if I change the source to “c:\dll2\……” (and copy the DLL there)

[srs_total_visitors]

 

Cannot resolve the ‘schemaLocation’ attribute

Biztalk are often quite troublesome with XSD schema imports

I had recently the problem with the Italian PA-Xml format, which imports the standard w3 xmldsig schema

When working with schema imports in Biztalk, it is not uncommon to receive the error “Cannot resolve the ‘schemaLocation’ attribute”, which simply means, that Biztalk cannot load the imported schema.

There are several possible reasons for this error:

  • The schema location is an URL. I have never yet seen an imported schema work in Biztalk if the schemalocation is an URL. Download the imported schema, include it in the project and modifiy the schemalocation to point to the local file
  • In the “xsd:import” element, the namespace attribute are listed before the schemalocation attribute. Biztalk seems to prefer that the schemalocation attribute is listed first
  • When modifying the schemalocation to point to a local file (same directory as the .XSD file), Biztalk sometimes prefer if you put “./” before the filename of the imported schema

I have yet not found a clear rule for when to do what – except that an URL in the schemalocation will never work.

With the Italian PAXml format, neither of these points work. Whatever I did, Biztalk kept informing me, that it “could not resolve the schemalocation” attribute.

What worked for me, was to create a folder in the Biztalk Schema project, and place the PAXml schema as well as the xmldsig schema in this folder. This folder in the project, does not have any practical impact when using this schema. When I did this, the schema project compiled without problems.

References:

https://social.msdn.microsoft.com/Forums/en-US/7ae8ac29-7f60-447b-8e4e-a470dd21e259/cannot-resolve-the-schemalocation-attribute?forum=biztalkgeneral

Biztalkmessages.wordpress.com

[srs_total_visitors]

New SFTP adapter in Biztalk 2016

WCF WinSCP-based SFTP Adapter

Since Microsoft now supplies a SFTP Adapter with Biztalk Server 2016, we thought we might as well try it out instead of using the Blogical one (which we have had several problems with).

It is relatively straightforward to use it in static sendports and receivelocations – the only thing we miss, is a possibility to use temporary filenames during transmission, which it does not support – only temporary folders.

However, it does not seem to be documented how to use the adapter in a dynamic sendport (at least, we have been unable to find it)

As documented, the SFTP Adapter uses the WinSCP program to do its actual work and specifically, it requires the WinSCPnet.dll to do its work.

Microsoft have implemented the WCF adapter in this DLL which wraps the calls to WinSCP.

C:\Program Files (x86)\Microsoft BizTalk Server 2016\Microsoft.BizTalk.Adapter.Sftp.dll – Assembly version 3.0.1.0

The Adapter loads the WinSCPnet DLL in the class SftpHelper.

The dynamic properties can be found in the class SftpOutputChannel Method AddPropertiesFromMessageContext.

Context Property Description
http://schemas.microsoft.com/BizTalk/2012/Adapter/sftp-properties#ClientAuthenticationMode this.sendPropertyBag.ClientAuthenticationMode = (ClientAuthenticationMode) Enum.Parse(typeof (ClientAuthenticationMode), property.Value.ToString());

enum Password, PublicKeyAuthentication, MultiFactorAuthentication

http://schemas.microsoft.com/BizTalk/2012/Adapter/sftp-properties#UserName Either specify UserName and Password, or specify a keyfile in PrivateKeyFile
http://schemas.microsoft.com/BizTalk/2012/Adapter/sftp-properties#Password Password
http://schemas.microsoft.com/BizTalk/2012/Adapter/sftp-properties#PrivateKeyFile Full path to Private Key
http://schemas.microsoft.com/BizTalk/2012/Adapter/sftp-properties#PrivateKeyPassword Password to the private key, if it is password protected
http://schemas.microsoft.com/BizTalk/2012/Adapter/sftp-properties#AccessAnyServerHostKey If False, host fingerprint must be specified. If True, all hosts will be accepted
http://schemas.microsoft.com/BizTalk/2012/Adapter/sftp-properties#SSHServerHostKeyFingerPrint Specify the fingerprint of SSH servers HostKey, if “AccessAnyServerHostKey” is False
http://schemas.microsoft.com/BizTalk/2012/Adapter/sftp-properties#AppendIfExists If False, transfer will fail if a file with the filename already exists. If True, the contents will be appended to the existing file

We have not been able to make the “AppendIfExists” work. If this property is true, the adapter issues a get command for the filename, and if the file does not exist yet (first transfer), then the transmission fails.

http://schemas.microsoft.com/BizTalk/2012/Adapter/sftp-properties#TargetFileName The name of the file created on the SFTP server. You can use the macros specified below
http://schemas.microsoft.com/BizTalk/2012/Adapter/sftp-properties#TemporaryFolder this.sendPropertyBag.TemporaryFolder = property.Value.ToString();
http://schemas.microsoft.com/BizTalk/2012/Adapter/sftp-properties#ProxyAddress Address of a proxy-server, if you need to use one, or blank.
http://schemas.microsoft.com/BizTalk/2012/Adapter/sftp-properties#ProxyUserName Username and password for the proxy-server, or blank if they are not needed
http://schemas.microsoft.com/BizTalk/2012/Adapter/sftp-properties#ProxyPassword
http://schemas.microsoft.com/BizTalk/2012/Adapter/sftp-properties#ProxyPort Port for the proxy-server
http://schemas.microsoft.com/BizTalk/2012/Adapter/sftp-properties#ProxyType this.sendPropertyBag.ProxyType = (Microsoft.BizTalk.Adapter.Sftp.ProxyType) Enum.Parse(typeof (Microsoft.BizTalk.Adapter.Sftp.ProxyType), property.Value.ToString());

enum None, SOCKS4, SOCKS5, HTTP

http://schemas.microsoft.com/BizTalk/2012/Adapter/sftp-properties#EncryptionCipher this.sendPropertyBag.EncryptionCipher = (EncryptionCipher) Enum.Parse(typeof (EncryptionCipher), property.Value.ToString());

enum Auto, AES, Arcfour, BlowFish, TripleDES, DES

http://schemas.microsoft.com/BizTalk/2012/Adapter/sftp-properties#Log this.sendPropertyBag.Log = property.Value.ToString();
http://schemas.microsoft.com/BizTalk/2003/system-properties#OutboundTransportLocation Contains the full EndPointAddress of the server – on the form sftp://servername:port

this.ParseSftpUrl(property.Value.ToString());

OutboundTransportLocation needs to be ‘sftp’ (case insensitive compare)

Supported macros

Macro Value
Use fixed context-property
%MessageID% http://schemas.microsoft.com/BizTalk/2003/system-properties#BizTalkMessageID
%SourceFileName% http://schemas.microsoft.com/BizTalk/2003/file-properties#ReceivedFileName
Use the macro as the name of a context-property
Use the contextproperty with the namespace namespace http://schemas.microsoft.com/BizTalk/2003/system-properties# with the macroname (nix the percent-signs) appended
%DestinationParty%
%DestinationPartyQualifier%
%SourceParty%
%SourcePartyQualifier
These macros formats either DateTime.Now or DateTime.UtcNow in the specified format
%datetime% yyyy-MM-ddThhmmss
%datetime_bts2000% yyyyMMddhhmmssf
%datetime.tz% yyyy-MM-ddThhmmsszzz
%time% hhmmss
%time.tz% hhmmsszzz

Biztalk – Orchestrations gather up in status “Ready to run”

In a previous post, I wrote about a situation we encountered on our production Biztalk 2016 system.

After a series of not-Biztalk related issues (including the MSDTC service crashing), we ended up in a situation, where one of our orchestrations would not process any messages. Existing instances would not process, and new instances would only pile up in status “Ready to run”. NONE of them would change to status “Active”, even after restarting the hostinstance bound to that orchestration.

We could not see any hints anywhere (like eventlog on Biztalk- and SQL server, SQL-server locks or similar) as to why the orchestrations would not be processed.

We run a ESB-like architecture, and since that orchestration was part of the main ESB itinerary, no documents could in fact be fully processed.

In the end, we were able to get most the of the orchestration serviceinstances processed, by Suspending all the serviceinstances in status “Ready to Run” and then “Resubmit” them 1 or 2 at a time. If we took more than a few, the situation would lock up again, and we again would have to Suspend all and resubmit them a few at a time.

In the end we had 5 serviceinstances that simply would not process. These 5 were terminated (they were already saved in our Tracking) and after a hostinstance restart (that may not have  been necessary), the orchestration would once again process messages without problems.

Another suggestion we had to resolve this situation, was to remove and then recreate all hostinstances for the host which had problems. We did not try this method.