BizTalk - comparing bindings files with excel source

We dynamically generate BizTalk bindings during in our build process. The process uses a binding file and replaces urls and thumbprints with values from a an excel spreadsheet.

The process for updating both of these files is manual so it is natural to find they get out of synch. Manually browsing a binding file is not an easy task so I wrote a powershell script that extracts and compares portnames between the two files.

In our case only the sendports get dynamically updated.
First extract the portnames from the binding file:

  $BindingFileName ="D:\bindings\App1~Binding~Template.xml"
  if ( -not (Test-Path -path $BindingFileName) ){
      "Can't Find"
      $BindingFileName
 exit
 } 
# Load binding information from file
$Bindings = [xml](get-content $BindingFileName)

# extract the ports
$BindingPorts = select-xml $Bindings -xpath "//SendPort" 

# get just the port names
$BindingPortNames = $BindingPorts | foreach { $_.node.Name } | sort-object –Unique 

Now that I have a sorted list of ports from the binding file, I need to get the ports from the excel file. Excel does not need to be installed on the server but the Excel drivers for oledb do need to be installed. Note: This driver is a 32 bit driver. use the -runas parameter or run in the x86 ISE.

$ExcelFileName = "D:\excel\App1~PortConfig.xls"
 
$OleDbConn = New-Object "System.Data.OleDb.OleDbConnection"
$OleDbCmd = New-Object "System.Data.OleDb.OleDbCommand"
$OleDbAdapter = New-Object "System.Data.OleDb.OleDbDataAdapter"
$DataTable = New-Object "System.Data.DataTable"

$OleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""$ExcelFileName"";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
$OleDbConn.Open()

$OleDbCmd.Connection = $OleDbConn
$OleDbCmd.commandtext = "Select Port from [SENDPORT_WCF$]"
$OleDbAdapter.SelectCommand = $OleDbCmd

$RowsReturned = $OleDbAdapter.Fill($DataTable)
$OleDbConn.Close()

#Output something so user sees something is happening 
ForEach ($row in $DataTable) {
Write-host $row.Port
}

$ExcelPortNames = $DataTable | foreach {$_.Port} | sort-object –Unique

The excel file has different tabs for different send ports. This line:

$OleDbCmd.commandtext = "Select Port from [SENDPORT_WCF$]"

specifies which column (Port) to select from which sheet [SENDPORT_WCF$]. 

The final step is to compare the two lists of portnames.

Compare-Object $ExcelPortNames $BindingPortNames -includeequal | ft inputobject, @{n="file";e={ if ($_.SideIndicator -eq '=>') { "binding" } else {if ($_.SideIndicator -eq '<=')  { "excel" } else {"=="}} }} | Out-File "d:\temp\Bank 2.0 bindings comparison.txt" 

Note that this is one long line that has been wrapped by the blog software.
The final result is a table that looks like this:

InputObject         file   
-----------         ----   
WcfSendPort_app1 ==     
WcfSendPort_app2 ==     
WcfSendPort_app3 ==     
WcfSendPort_appx binding
WcfSendPort_appy binding
WcfSendPort_appz binding
WcfSendPort_app1z excel  
WcfSendPort_app2z excel  

I'll leave it as an exercise for the reader to process a whole directory full of bindings files. :). One could also add parameters and send the file names on the comand line.

The complete code:

#Param(
#  [string]$BindingFileName
#)

#Binding files
  $BindingFileName ="D:\bindings\App1~Binding~Template.xml"
  if ( -not (Test-Path -path $BindingFileName) ){
      "Can't Find"
      $BindingFileName
 exit
 } 
 
# Load binding information from file
$Bindings = [xml](get-content $BindingFileName)

# extract the ports
$BindingPorts = select-xml $Bindings -xpath "//SendPort" 

# get just the port names
$BindingPortNames = $BindingPorts | foreach { $_.node.Name } | sort-object –Unique 
 
#EXCEL 
 
$ExcelFileName = "D:\excel\App1~PortConfig.xls"
 
$OleDbConn = New-Object "System.Data.OleDb.OleDbConnection"
$OleDbCmd = New-Object "System.Data.OleDb.OleDbCommand"
$OleDbAdapter = New-Object "System.Data.OleDb.OleDbDataAdapter"
$DataTable = New-Object "System.Data.DataTable"

$OleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""$ExcelFileName"";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
$OleDbConn.Open()

$OleDbCmd.Connection = $OleDbConn
$OleDbCmd.commandtext = "Select Port from [SENDPORT_WCF$]"
$OleDbAdapter.SelectCommand = $OleDbCmd

$RowsReturned = $OleDbAdapter.Fill($DataTable)
$OleDbConn.Close()

#Output something so user sees something is happening 
ForEach ($row in $DataTable) {
Write-host $row.Port
}

$ExcelPortNames = $DataTable | foreach {$_.Port} | sort-object –Unique

#COMPARISON

Compare-Object $ExcelPortNames $BindingPortNames -includeequal | ft inputobject, @{n="file";e={ if ($_.SideIndicator -eq '=>') { "binding" } else {if ($_.SideIndicator -eq '<=')  { "excel" } else {"=="}} }} | Out-File "d:\temp\App1_bindings_comparison.txt"