BizTalk PowerShell to Restart Host, Run SQL, re-trigger SQL Polling

I found myself doing three boring tasks over and over again after each deploy:

  1. Restarting a BizTalk Host Instance
  2. Running a SQL command
  3. Disable/re-Enable the Receive Location for SQL Polling.

I could potentially add a build/deploy step at the top, to combine that as well… maybe one of these days soon…

To force the SQL polling to run, I had to run a SQL command to change the value of a certain column in one or more rows.

The polling receive port was set to poll every 300 seconds (i.e. 5 minutes). By disabling it and enabling it again, the polling will happen immediately. No one wants to wait an average of 2.5 minutes for their polling to happen!

I also realized that if I got any of these steps done in the wrong order, I might accidentally be re-running with an old DLL, and definitely didn’t want that to happen.

There are a few parms at the top you can set.
I probably should have made the SQL connection strings a parm too.

If I didn’t re-deploy, and just want to poll again, I can set the ynRestartHostInstance to “N”. For example, I might just change the ID of the items to poll. But even then, I would want to disable/enable the Receive Location to make the polling happen faster.


$ynRestartHostInstances = "N" 
$ynSetSQLToPoll = "Y" 
$appName = "MySuperApp"
$rcvLocation = "rlSQLPolling"

if ($ynRestartHostInstances -eq "Y") 
    cd "Biztalk:\Platform Settings\Host Instances"
    Get-ChildItem | ft -auto   

    $hostName = "Microsoft BizTalk Server BizTalkOrchestrations64 dlBizTalkDev1"
    Write-Host "About to start/stop $hostName"              

    Stop-HostInstance $hostName 
    Write-Host "Host Instance Stopped" 

    Start-HostInstance $hostName 
    Write-Host "Host Instance Started"

if ($ynSetSQLToPoll -eq "Y") 

    $datasource = "server=server\instance;database=mydb;trusted_connection=true"

    #if not using Integratd Security, you might want to pass user/pass in variables or prompt for them 
    #$connectionString = 'User Id=' + $username + ';Password=' + $password + ';' + $datasource
    $connectionString = $datasource
    write-host $connectionString 
    $connection = New-Object System.Data.SQLClient.SQLConnection($connectionString)

    # -- reset so polling will pick them up 
    $updateCommand = "update PollingTable set entryStatus = 'New' where ID in (101, 102)  "

    Write-Host $updateCommand 

    $Command = New-Object System.Data.SQLClient.SQLCommand 
    $Command.Connection = $connection 
    $Command.CommandText = $updateCommand 

    $rowsAffected = $Command.ExecuteNonQuery() 
    Write-Host ("Rows Affected by Update=$rowsAffected") 

    # disable/re-enable ReceiveLocation to kick off polling faster 
    Write-Host "Handling ReceiveLocation next:" 
    #cd "Biztalk:\Applications\$($appName)\Receive Locations" 
    cd "Biztalk:\Applications\$appName\Receive Locations"
    Get-ChildItem | ft -auto   

    Write-Host "About to restart $rcvLocation"              

    Disable-ReceiveLocation $rcvLocation
    Enable-ReceiveLocation  $rcvLocation


Leave a Reply