Using the ‘Microsoft OpenXml SDK’
https://docs.microsoft.com/en-us/office/open-xml/spreadsheets
Older method using ODBC etc
http://biztalk.stottcreations.com/2007/12/biztalk_excel_xls_pipeline_component/
Category: Biztalk
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 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
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:
[srs_total_visitors]
Troubleshoot MSDTC issues
Howto change time on a Biztalk Server
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.