Sometimes you ask yourself what version of BizTalk is running on a certain server. Maybe you’re a consultant or employee stepping into a new job/contract and you are trying to get the lay of the land. Or maybe you have some old servers that are not documented.

The following script will answer that question. Might save keystrokes of opeining Control Panel or RegEdit to find out.

See this page to get a list of all the BizTalk versions. Hope you don’t find anything older than BizTalk 2010!

cls
#read the registry 
$item = Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\BizTalk Server\3.0"
Write-Host "Biztalk Internal Version: $($item.ProductVersion)"
$major,$minor, $x, $y = $item.ProductVersion.split('.') 
#Write-Host "$major $minor" 

# see https://social.technet.microsoft.com/wiki/contents/articles/7915.biztalk-server-versions.aspx 

$version = switch ($minor)
{
    13 {"2019"}
    12 {"2016"}
    11 {"2013/R2"}
    10 {"2013 (not R2)"}
    9 {"2010"} 
    8 {"2009"} 
    6 {"2006/R2"} 
    5 {"2006 (not R2)"} 
    0 {"2000-2004 Past Time to Upgrade! "} 
}

Write-Host "BizTalk Common Name: $version" 
Write-Host "Product Edition: $($item.ProductEdition)" 

Example Output:

Biztalk Internal Version: 3.10.229.0
BizTalk Common Name: 2013 (not R2)
Product Edition: Enterprise

BizTalk has a schedule job that does incremental backups, usually every 15 minutes. But what if you want to do a full backup immediate?

Run this command in SQL Server Management Studio (SSMS):

USE BizTalkMgmtDb
EXEC sp_ForceFullBackup

Then manually start the SQL agent job “Backup BizTalk Server (BizTalkMgmtDb)” (or wait until the next scheduled 15 minute interval).

The above is required if you get in the situation where you see this error in the 3rd step of the above backup agent job:

Error: BACKUP LOG cannot be performed because there is no current database backup.

Full error looks something like this:

Send Port: “sp_Vendor_Vendor_FilesToSendToAS2” URI: “http://as2.vendorsite.com/”
Reason: The Encryption Certificate has not been configured for AS2 party. AS2-From: YourAS2ID AS2-To: VendorAS2ID

Solution:
Open the send port, click the “Certificate” item on the left.
Then click the “Browse” button under the certificate, and select the certificate that applies to your vendor. The certificate has to be first stored in the “Local Computer/Other People Store”, as described on this MSDN doc.

The selection for the certificate will look something like this. I had to black out our actual vendor/certificate names.

test

This script can be scheduled to create a nightly backup of all the bindings and MSIs for all the applications in BizTalk. It uses the Powershell Extensions for BizTalk to enumerate the application names, then calls BTSTask once for the bindings, and once for the MSI.

It gives you the ability to customize your backup folder names and files names. For example, if your application name is “ABC.Orders”, the backups are:
ABC.Orders_2019_05_17__09_10_21.xml
ABC.Orders_2019_05_17__09_10_21.msi

You could make changes to create a new folder for each set of backups, example: Backups_2019_05_17__09_10_21.

Another enhancement could be to zip the archives, or to purge archives over x days.

There is one trick. You may also want to backup the Parties. I’m currently on BizTalk 2013, and I know there are some improvements for supporting importing/exporting parties individually in 2016. Given that I’m on BT2013, I decided to backup the parties just once. So if the application name = “BizTalk EDI Application”, I add the parameter for the party backup, and they will be in that file. You could also create a dummy empty application and use it.

<#

       Author: Neal Walters 
  Description: Backup BizTalk Applications (both Bindings and Application/MSIs) via Script 
         Date: May 2019 

#>

Add-PSSnapIn -Name BiztalkFactory.PowerShell.Extensions
#This next line only needed when SQL server is not on same machine as BizTalk
New-PSDrive -Name BizTalk -Root BizTalk:\ -PsProvider BizTalk -Instance "YourSQLServer" -Database BizTalkMgmtDb

$exportFolder = "c:\Backup\BizTalk" 
$server = "YourSQLServer"
$database = "BizTalkMgmtDB"

cls

$apps = Get-ChildItem -Path "Biztalk:\Applications"

foreach ($app in $apps) 
    {
        Write-Host "===== Application: $($app.Name) ==== " 

        if ($app.Name -ne "BizTalk.System")   # avoid error trying to export this app 
          {
            $fmtDateTime = $(get-date -f yyyy_MM_dd__HH_mm_ss)
            $bindingsDestFilename = "${exportFolder}\$($app.Name)_$fmtDateTime.xml" 
            $msiAppDestFilename = "${exportFolder}\$($app.Name)_$fmtDateTime.msi" 
           
            write-host $bindingsDestFilename 
            $allArgs = @("ExportBindings", "/Destination:$bindingsDestFilename",  "/ApplicationName:$($app.Name)",
                "/Server:$server", "/Database:$database") 
            if ($app.Name -eq "BizTalk EDI Application") 
            {
               #just backup the parties one time with this one app 
               $allArgs += "/GlobalParties"
            }

            write-host "Args=$allArgs"
            & "c:\Program Files (x86)\Microsoft BizTalk Server 2013\BTSTask.exe" $allArgs

            $allArgs = @("ExportApp", "/Package:$msiAppDestFilename",  "/ApplicationName:$($app.Name)",
                "/Server:$server", "/Database:$database") 
            write-host "Args=$allArgs"
            & "c:\Program Files (x86)\Microsoft BizTalk Server 2013\BTSTask.exe" $allArgs
          }
    }

 

The purpose of this example is to demonstrate two ways to process Oracle data with a SQL query. First is by using the DataReader (streaming) and the second is by using the DataAdapter (fills an object in memory).

Then as a bonus, we convert that data to HTML and email it.
For an example of how to format the email with pretty bordered-tables and colors, please see Powershell Monitoring and Email of EventLog errors/

In the real world, you will use one method or the other, i.e. DataReader or DataAdapter. In this example, there was built-in functionality to turn the DataTable filled in by the DataAdapter into an HTML table.

The SQL that I crafted just creates two rows with three columns, an doesn’t access any real tables. But it does rely on an Oracle Server to process this SQL. So to run this code, you will have to have security and be able to connect to an Oracle Server, but you won’t need any special tables.

Add-Type -Path "c:\Program Files\PackageManagement\NuGet\Packages\Oracle.ManagedDataAccess.Core.2.18.6\lib\netstandard2.0\Oracle.ManagedDataAccess.dll"
cls 
$username = "xxxxx"
$password = "xxxxx"
#$datasource = Read-Host -Prompt "Enter database TNS name"
$datasource = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Dev.YourServer.com)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=YourService)))"

$query = @"
   select 'A1' as col1, 'B1' as col2, 'C1' as col3 from dual 
     union 
   select 'A2' as col1, 'B2' as col2, 'C2' as col3 from dual 
"@  # must be in column 1 

$connectionString = 'User Id=' + $username + ';Password=' + $password + ';Data Source=' + $datasource
write-host $connectionString 
$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
$connection.open()


$command=$connection.CreateCommand()
$command.CommandText=$query
$reader=$command.ExecuteReader()
$rowNum = 0 
while ($reader.Read()) 
{
   $rowNum = $rowNum + 1 
   Write-Host "Row:$rowNum $($reader["Col1"])  $($reader["Col2"])  $($reader["Col3"]) "
}


Write-Host "`nTry Data Adapter " 
$dataAdapter = New-Object Oracle.ManagedDataAccess.Client.OracleDataAdapter 
$dataAdapter.SelectCommand = New-Object Oracle.ManagedDataAccess.Client.OracleCommand ($query, $connectionString) 


$ds = New-Object System.Data.DataSet 
$dt = New-Object System.Data.DataTable 
Write-Host "Fill" 
$dataAdapter.fill($ds) 
$connection.Close()

# Convert to HTML 
$HTMLmessage = $ds.Tables[0] |select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html  #same line 

$HTMLMessage = [string] $HTMLMessage 

Write-Host  "HTML:"
Write-Host  $HTMLmessage 

$fromEmail = "nwalters@YourServer.com"
$toEmail = "nwalters@YourServer.com"
$emailSubject = "Demo Email from Powershell"
$smtpServer = "smtp.YourServer.com"
send-mailmessage -from $fromEmail -to $toEmail -subject $emailSubject  -BodyAsHTML -body $HTMLmessage -priority High -smtpServer $smtpServer

The sample email viewed in Outlook is shown below. As mentioned int the first paragraph, it is possible to format the table more nicely. This is done with a cascading style sheet (CSS). Please see Powershell Email with CSS (from EventLog)/

Note, before writing the above script, I had to make sure the necessary components were installed. The following should do that:

Register-PackageSource -Name nuget.org -Location https://www.nuget.org/api/v2 -ProviderName NuGet
Install-Package Oracle.ManagedDataAccess.Core

I’m writing a custom deploy solution for BizTalk. If the application doesn’t exist, I need to call “BTSTask AddApp” to add it.

Sample:


Add-PSSnapIn -Name BiztalkFactory.PowerShell.Extensions

function checkApplicationExists ($appName) 
{
    cd "Biztalk:\Applications"
    $apps = Get-ChildItem
    $boolFoundMatch = $false 
    foreach ($app in $apps) 
      {
         Write-Host "  Testing $($app.Name) " 
         if ($app.Name -eq $appname) 
         {
            $boolFoundMatch = $true 
         }
      }
    return $boolFoundMatch 
  
}

cls
$app1Exists = checkApplicationExists "Test.Vendors"
Write-Host "REI.Vendors $app1Exists" 

$app2Exists = checkApplicationExists "Test.Nonexistent"
Write-Host "REI.Vendor.TestDeploy $app2Exists" 


PowerShell extensions allow you to interact with BizTalk Artificacts. For example, you can like list applications, sendports, receive ports, host instances, and enable/disable them in PowerShell.

First run the InstallUtil

C:\Windows\system32>c:\Windows\Microsoft.NET\Framework64\v4.0.30319\InstallUtil.exe "c:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\Utilities\PowerShell\BizTalkFactory.PowerShell.Extensions.dll"

Output:
Microsoft (R) .NET Framework Installation utility Version 4.7.2558.0
Copyright (C) Microsoft Corporation. All rights reserved.

Running a transacted installation.

Beginning the Install phase of the installation.
See the contents of the log file for the c:\Program Files (x86)\Microsoft BizTal
k Server 2013\SDK\Utilities\PowerShell\BizTalkFactory.PowerShell.Extensions.dll
assembly’s progress.
The file is located at c:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\
Utilities\PowerShell\BizTalkFactory.PowerShell.Extensions.InstallLog.
Installing assembly ‘c:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\Ut
ilities\PowerShell\BizTalkFactory.PowerShell.Extensions.dll’.
Affected parameters are:
logtoconsole =
assemblypath = c:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\Utili
ties\PowerShell\BizTalkFactory.PowerShell.Extensions.dll
logfile = c:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\Utilities\
PowerShell\BizTalkFactory.PowerShell.Extensions.InstallLog

The Install phase completed successfully, and the Commit phase is beginning.
See the contents of the log file for the c:\Program Files (x86)\Microsoft BizTal
k Server 2013\SDK\Utilities\PowerShell\BizTalkFactory.PowerShell.Extensions.dll
assembly’s progress.
The file is located at c:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\
Utilities\PowerShell\BizTalkFactory.PowerShell.Extensions.InstallLog.
Committing assembly ‘c:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\Ut
ilities\PowerShell\BizTalkFactory.PowerShell.Extensions.dll’.
Affected parameters are:
logtoconsole =
assemblypath = c:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\Utili
ties\PowerShell\BizTalkFactory.PowerShell.Extensions.dll
logfile = c:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\Utilities\
PowerShell\BizTalkFactory.PowerShell.Extensions.InstallLog

The Commit phase completed successfully.

The transacted install has completed.

Next, in Powershell (assuming you have already set your execution policy):

If your SQL Server is on same machine as BizTalk this should work:

Add-PSSnapIn -Name BiztalkFactory.PowerShell.Extensions

#Verify it works by listing the BizTalk Applications 
cd "Biztalk:\Applications\"
Get-ChildItem | ft -auto 

If you SQL Server is on a different machine than the BizTalk server, then add the New-PSDrive statement as below (StackOverflow)

Add-PSSnapIn -Name BiztalkFactory.PowerShell.Extensions
New-PSDrive -Name BizTalk -Root BizTalk:\ -PsProvider BizTalk -Instance MySqlServer -Database BizTalkMgmtDb

#Verify it works by listing the BizTalk Applications 
cd "Biztalk:\Applications\"
Get-ChildItem | ft -auto 
The goal of this program is to: 1) Rename the file to include the PONum from inside the file 2) Pretty-Print (format) the XML inside the file 3) Preserve (keep) the original date/time of the file
cls

function Format-XMLDoc ([xml] $xml, $indent=3) 
{ 
    $StringWriter = New-Object System.IO.StringWriter 
    $XmlWriter = New-Object System.XMl.XmlTextWriter $StringWriter 
    $xmlWriter.Formatting = “indented” 
    $xmlWriter.Indentation = $Indent 
    $xml.WriteContentTo($XmlWriter) 
    $XmlWriter.Flush() 
    $StringWriter.Flush() 
    Write-Output $StringWriter.ToString() 
}


function Format-XML ($xmlString, $indent=3) 
{ 
    [xml]$xml.LoadXml($xmlString) 
    $StringWriter = New-Object System.IO.StringWriter 
    $XmlWriter = New-Object System.XMl.XmlTextWriter $StringWriter 
    $xmlWriter.Formatting = “indented” 
    $xmlWriter.Indentation = $Indent 
    $xml.WriteContentTo($XmlWriter) 
    $XmlWriter.Flush() 
    $StringWriter.Flush() 
    Write-Output $StringWriter.ToString() 
}

$folderName = "c:\BizTalk\Messaging\Archive\SentToDropShipOrchBackup"
$files = Get-ChildItem $folderName -Filter "Test*.xml" -File  | Sort-Object LastWriteTime -Descending

$maxFiles = 1   #used to test with a small number of files first 
$fileCount = 0 


foreach ($file in $files) 
{
     $fileCount = $fileCount + 1 
     if ($fileCount -gt $maxFiles) 
        {
          exit 
        }
     Write-Host $fileCount $file.Name 
     Write-Host "Creation    Time: $($file.CreationTime)"
     Write-Host "Last Write  Time: $($file.LastWriteTime)"
     Write-Host "Last Access Time: $($file.LastAccessTime)"

     $origCreationTime  =  $file.CreationTime
     $origLastWriteTime =  $file.LastWriteTime

     $fileContents = [IO.File]::ReadAllText($file.FullName)
     [xml]$xml.LoadXml($fileContents)  #load into XmlDoc 


     $fileContentsFormatted = Format-XMLDoc $fileContents -indent 4
     Write-Host $fileContentsFormatted
     [IO.File]::WriteAllText($file.FullName, $fileContentsFormatted)

     $PONum = $xml.ShipDocDetails.ShipDocDetails_Child1.rcPONumber_d
     $PONum=$PONum.Replace('"','');
     Write "PONum=$PONum" 

     $newGuid = [guid]::NewGuid()
     $newFileName = "ShipDoc_PONum_${PONum}_$newGuid.xml"
     Write-Host "NewName=$newFileName"

     Rename-Item $file.FullName $newFileName 
     (Get-ChildItem "${folderName}\${newFileName}").CreationTime = $origCreationTime
     (Get-ChildItem "${folderName}\${newFileName}").LastWriteTime = $origLastWriteTime

}

Goal was to …

Requirements: I have some files from an external system, and I want to promote some data from inside the file to the file name itself.

Example, original filename: PO_287783.xml
Desired filename: PO_287783_TransID_9081631_Lines_3.xml

I want to peek into the file an pull out the Transaction ID, and also get a count of how many line items are on this PO. Each line item in this case is in an XML element called G_LINES.

In this specific example, the TransactionID is not the value of an XML element, but only a partial value, so have to do some parsing. It’s found in a row like this:

<POH_NOTE_TO_VENDOR>10998-9081635||X</POH_NOTE_TO_VENDOR> 
I want to be able to run the script multiples times, when no files appear in the directory, and it shouldn’t try to rename files that have already been renamed (so it checks for the presence of the phrase “TRANS” in the filename).

Sample Code:

cls
$retentionDays = 30
$formatDateTime = get-date -f _yyyy_MM_dd__HH_mm_ss
$formatDate = get-date -f _yyyy_MM_dd
Write-Host “formatDateTime= $formatDateTime”
Write-Host “——————————————————-”
$renameCount = 0
$skipCount = 0
$deleteCount = 0

# Array of Directory Names (logic below will process each directory you specify here)
$dirNames = “c:\temp\BizTalkTest\PORouting\InBoxFromFTP”  #you can add comma separately list of directories here 

Foreach ($dirName in $dirNames)
{
    Write-Host “DirName=$dirName”
    $files = Get-ChildItem $dirName -Filter *.xml 
    ForEach ($file in $files) 
    {
       
        $fileContents = [System.IO.File]::ReadAllText($file.FullName)

        #example: <POH_NOTE_TO_VENDOR>10998-9081635||X</POH_NOTE_TO_VENDOR> 

        $lineHeader = "<G_LINES>"
        $countLines = ([regex]::Matches($fileContents, "$lineHeader" )).count
        Write-Host "countLines=$countLines" 


        $posTransIDInFileName = $file.Name.IndexOf("Trans") 
        if ($posTransIDInFileName -eq -1)   #if file already had Trans in the filename, don't rename it again 
        {

            $searchString = "<POH_NOTE_TO_VENDOR>10998-"
            $pos1 = $fileContents.IndexOf($searchString); 
            if ($pos1 -gt 0) 
              {
                 $pos2 = $fileContents.IndexOf("||",$pos1) 
                 if ($pos2 -gt 0) 
                 {
                    $startCapture = $pos1 + $searchString.Length 
                    $captureLength = $pos2 - $startCapture 
                    Write-Host "StartCapture=$startCapture CaptureLength=$CaptureLength" 
                    $TransID = $fileContents.Substring($startCapture,$captureLength); 
                    Write-Host "TransId=" + $TransID 

                    $newFilename = $file.Name.Replace(".xml","_TransID_$TransID.xml"); 
                    $newFilename = $file.Name.Replace(".xml","_Lines_$countLines.xml"); 
                    write-Host $newFilename
                    Rename-Item $file.FullName $newFileName
                 }
              }
         }

       


    } #end of ForEach $file 


}  #end of ForEach $dirName 
</code>

Every case is unique, but here’s what happened to me.

My goal was to receive a CSV file, and have it split into multiple output files in the Send Port. I did specify my CSV flat-file pipeline in my Receive Location’s Pipeline.

I took over a project at a new client. And while re-writing the code, I changed the names of all the programs and namespaces from XYZLongCompany, to just XYZ.

When I built the receive location on the test system, I copied the old long names, instead of the new short names into my Receive Location Pipeline properties, where you specify the “Document Spec Name” and the “Header Spec Name”.

This MSDN post shows how to find the document spec name by expanding the schemas then showing the properties. I can’t include any screen shots with actual client name.